The block is the smallest storage allocation unit in oracle.
The block is the smallest storage allocation unit in oracle. The size of the database block is defined with the db_block_size parameter:
SYS@MNTMP1 SQL> @pd db_block_size
Show all parameters and session values from x$ksppi/x$ksppcv...
INDX I_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------- ------------------------------ ----------------------------------------------------------------------
829 33D db_block_size 8192 Size of database block in bytes
The data block consits of:
SYS@MNTMP1 SQL> @pd db_block_size
Show all parameters and session values from x$ksppi/x$ksppcv...
INDX I_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------- ------------------------------ ----------------------------------------------------------------------
829 33D db_block_size 8192 Size of database block in bytes
The data block consits of:
The header stores the information about the block address and segment type (index, table...)
Table directory stores the information about the tables which have rows in this block.
Row directory stores the information about the actual rows
The header,table directory and row directory are called overhead. The typical size of the overhead is somewhere between 84 and 107 bytes.
To know the relative fie number and block number of a row, you can use:
MNISCAK@MNTMP1 SQL> select object_id,dbms_rowid.ROWID_RELATIVE_FNO(rowid) RFILE#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) BLOCK# from t where rownum=1;
OBJECT_ID RFILE# BLOCK#
---------- ---------- ----------
20 5 7939
So my row number 1 is stored in the datafile 5, block 7939.
Now, we can dump the block:
My trace file:
MNISCAK@MNTMP1 SQL> def trc
DEFINE TRC = "/oracle/oraspre/diag/rdbms/mntmp/MNTMP1/trace/MNTMP1_ora_45140.trc" (CHAR)
MNISCAK@MNTMP1 SQL> alter system dump datafile 5 block 7939;
System altered.
In the tracefile we can see:
Start dump data blocks tsn: 5 file#:5 minblk 7939 maxblk 7939
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5 rdba=20979459
BH (0x6dfb4bb8) file#: 5 rdba: 0x01401f03 (5/7939) class: 1 ba: 0x6dd24000
set: 45 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 8,22
dbwrid: 0 obj: 80644 objn: 80644 tsn: 5 afn: 5 hint: f
hash: [0x9d7c6100,0x9d7c6100] lru: [0x6dfb4e48,0x9bb614e0]
ckptq: [NULL] fileq: [NULL] objq: [0x6dfb4e70,0x6e3b0bd0] objaq: [0x6dfb4e80,0x9140f8d8]
st: SCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1 le: 0x743e0f78
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 5 rdba: 0x01401f03 (5/7939)
scn: 0x0000.00106634 seq: 0x02 flg: 0x04 tail: 0x66340602
frmt: 0x02 chkval: 0x01a5 type: 0x06=trans data
This is the start of the dump. We can see, that it's dump of datafile #5 and block# 7939.
rdba is the relative data block address and its 5/7939
Obj:80644 is the object_number of the segments
scn: 0x0000.00106634 - is the currebt system change number
The tail of the data block consists of the last two bytes of scn (6634) the sequence (02) and type (06). If decomposition of the tail does not match those values oracle knows that the block is inconsistent and needs recovery.
Scroll down and you will see:
Block header dump: 0x01401f03
Object id on Block? Y
seg/obj: 0x13b04 csc: 0x00.10662c itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1401f00 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.0000110f 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01401f03
data_block_dump,data header at 0x7f18b1cbb27c
===============
tsiz: 0x1f80
hsiz: 0xc2
pbl: 0x7f18b1cbb27c
76543210
flag=--------
ntab=1
nrow=88
frre=-1
fsbo=0xc2
fseo=0x432
avsp=0x370
tosp=0x370
0xe:pti[0] nrow=88 offs=0
0x12:pri[0] offs=0x1f33
0x14:pri[1] offs=0x1ee4
0x16:pri[2] offs=0x1e98
0x18:pri[3] offs=0x1e4b
seg/obj is the hex number of the segment which has rows in this block. You can change the hex to decimal and than check the data_object_id:
MNISCAK@MNTMP1 SQL> @dec 0x13b04
DEC HEX
----------------------------------- --------------------
80644.000000 13B04
MNISCAK@MNTMP1 SQL> select object_name from dba_objects where data_object_id=80644;
OBJECT_NAME
--------------------------------------------------
T
So, the 0x13b04 represents the segment T.
typ: 1 - DATA <--- It's the table
itc <--- number of interested transactions. you can see those transactions in the botom of the trace file.
tsiz <-- it's the number of bytes allocated to the block. 0x1f80 represents 8064 bytes.
hsiz <--0xc2 represents 194 bytes that are allocated to overhead.
ntab <-- number of tables stored in this block. Unless it's cluster, it will allways be 1
nrow <-- number of rows stored in this block
Starting wit the 0xe we have a directory to each row. We can see, tat the first row start at offset 0x1f33. With this address, the row can be found very quickly.
Nex we can see the actual data in the block:
block_row_dump:
tab 0, row 0, @0x1f33
tl: 77 fb: --H-FL-- lb: 0x0 cc: 14
col 0: [ 3] 53 59 53
col 1: [ 5] 49 43 4f 4c 24
col 2: *NULL*
col 3: [ 2] c1 15
col 4: [ 2] c1 03
col 5: [ 5] 54 41 42 4c 45
col 6: [ 7] 78 73 08 0a 0a 3a 11
col 7: [ 7] 78 73 08 0a 0b 04 31
col 8: [19] 32 30 31 35 2d 30 38 2d 31 30 3a 30 39 3a 35 37 3a 31 36
col 9: [ 5] 56 41 4c 49 44
col 10: [ 1] 4e
col 11: [ 1] 4e
col 12: [ 1] 4e
col 13: [ 2] c1 02
tl <-- number of bytes allocated by this row
cc <-- column count
fb <-- -H-FL- H- we have the header of the row, F - first piece of the row, L - last piece of the row.
When we have all those 3 signs what means that the row was not migrated
the rest of the informations are actual data.
Table directory stores the information about the tables which have rows in this block.
Row directory stores the information about the actual rows
The header,table directory and row directory are called overhead. The typical size of the overhead is somewhere between 84 and 107 bytes.
To know the relative fie number and block number of a row, you can use:
MNISCAK@MNTMP1 SQL> select object_id,dbms_rowid.ROWID_RELATIVE_FNO(rowid) RFILE#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) BLOCK# from t where rownum=1;
OBJECT_ID RFILE# BLOCK#
---------- ---------- ----------
20 5 7939
So my row number 1 is stored in the datafile 5, block 7939.
Now, we can dump the block:
My trace file:
MNISCAK@MNTMP1 SQL> def trc
DEFINE TRC = "/oracle/oraspre/diag/rdbms/mntmp/MNTMP1/trace/MNTMP1_ora_45140.trc" (CHAR)
MNISCAK@MNTMP1 SQL> alter system dump datafile 5 block 7939;
System altered.
In the tracefile we can see:
Start dump data blocks tsn: 5 file#:5 minblk 7939 maxblk 7939
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5 rdba=20979459
BH (0x6dfb4bb8) file#: 5 rdba: 0x01401f03 (5/7939) class: 1 ba: 0x6dd24000
set: 45 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 8,22
dbwrid: 0 obj: 80644 objn: 80644 tsn: 5 afn: 5 hint: f
hash: [0x9d7c6100,0x9d7c6100] lru: [0x6dfb4e48,0x9bb614e0]
ckptq: [NULL] fileq: [NULL] objq: [0x6dfb4e70,0x6e3b0bd0] objaq: [0x6dfb4e80,0x9140f8d8]
st: SCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1 le: 0x743e0f78
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 5 rdba: 0x01401f03 (5/7939)
scn: 0x0000.00106634 seq: 0x02 flg: 0x04 tail: 0x66340602
frmt: 0x02 chkval: 0x01a5 type: 0x06=trans data
This is the start of the dump. We can see, that it's dump of datafile #5 and block# 7939.
rdba is the relative data block address and its 5/7939
Obj:80644 is the object_number of the segments
scn: 0x0000.00106634 - is the currebt system change number
The tail of the data block consists of the last two bytes of scn (6634) the sequence (02) and type (06). If decomposition of the tail does not match those values oracle knows that the block is inconsistent and needs recovery.
Scroll down and you will see:
Block header dump: 0x01401f03
Object id on Block? Y
seg/obj: 0x13b04 csc: 0x00.10662c itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1401f00 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.0000110f 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01401f03
data_block_dump,data header at 0x7f18b1cbb27c
===============
tsiz: 0x1f80
hsiz: 0xc2
pbl: 0x7f18b1cbb27c
76543210
flag=--------
ntab=1
nrow=88
frre=-1
fsbo=0xc2
fseo=0x432
avsp=0x370
tosp=0x370
0xe:pti[0] nrow=88 offs=0
0x12:pri[0] offs=0x1f33
0x14:pri[1] offs=0x1ee4
0x16:pri[2] offs=0x1e98
0x18:pri[3] offs=0x1e4b
seg/obj is the hex number of the segment which has rows in this block. You can change the hex to decimal and than check the data_object_id:
MNISCAK@MNTMP1 SQL> @dec 0x13b04
DEC HEX
----------------------------------- --------------------
80644.000000 13B04
MNISCAK@MNTMP1 SQL> select object_name from dba_objects where data_object_id=80644;
OBJECT_NAME
--------------------------------------------------
T
So, the 0x13b04 represents the segment T.
typ: 1 - DATA <--- It's the table
itc <--- number of interested transactions. you can see those transactions in the botom of the trace file.
tsiz <-- it's the number of bytes allocated to the block. 0x1f80 represents 8064 bytes.
hsiz <--0xc2 represents 194 bytes that are allocated to overhead.
ntab <-- number of tables stored in this block. Unless it's cluster, it will allways be 1
nrow <-- number of rows stored in this block
Starting wit the 0xe we have a directory to each row. We can see, tat the first row start at offset 0x1f33. With this address, the row can be found very quickly.
Nex we can see the actual data in the block:
block_row_dump:
tab 0, row 0, @0x1f33
tl: 77 fb: --H-FL-- lb: 0x0 cc: 14
col 0: [ 3] 53 59 53
col 1: [ 5] 49 43 4f 4c 24
col 2: *NULL*
col 3: [ 2] c1 15
col 4: [ 2] c1 03
col 5: [ 5] 54 41 42 4c 45
col 6: [ 7] 78 73 08 0a 0a 3a 11
col 7: [ 7] 78 73 08 0a 0b 04 31
col 8: [19] 32 30 31 35 2d 30 38 2d 31 30 3a 30 39 3a 35 37 3a 31 36
col 9: [ 5] 56 41 4c 49 44
col 10: [ 1] 4e
col 11: [ 1] 4e
col 12: [ 1] 4e
col 13: [ 2] c1 02
tl <-- number of bytes allocated by this row
cc <-- column count
fb <-- -H-FL- H- we have the header of the row, F - first piece of the row, L - last piece of the row.
When we have all those 3 signs what means that the row was not migrated
the rest of the informations are actual data.