The Structure of the Row
Data inside the database is stored in rows. Each row will have a header and a tail in the definition and be stored as length: value format.
In the example below a field has been select from the EMP table to show how the row is stored.
SQL> SELECT ENAME FROM EMP
2 WHERE COMM=0;
ENAME
----------
TURNER
Now see how the data 'TURNER' is stored inside the database.
SQL> SELECT DUMP(ENAME) FROM EMP
2 WHERE COMM=0;
DUMP(ENAME)
--------------------------------------------
Typ=1 Len=6: 84,85,82,78,69,82
The following table, containing only a few rows, will be used to demonstate how oracle stores the rows in the data blocks.
SQL> SELECT *FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> SELECT *FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> CREATE TABLE DUMP_TEST AS
2 SELECT * FROM DEPT;
Table created.
SQL> SELECT FILE_ID,BLOCK_ID FROM
2 DBA_EXTENTS
3 WHERE
4 SEGMENT_NAME='DUMP_TEST'
5 AND
6* OWNER='SCOTT'
SQL> /
FILE_ID BLOCK_ID
---------- ----------
3 114
SQL> SELECT OBJ$
2 FROM OBJ$
3 WHERE
4 OBJECT_NAME=/
5
SQL> SELECT OBJECT_ID
2 FROM USER_OBJECTS
3 WHERE
4* OBJECT_NAME='DUMP_TEST'
SQL> /
OBJECT_ID
----------
3091
SQL> ALTER SYSTEM DUMP DATAFILE 3 BLOCK 114; ---- (Header)
System altered
SQL> ALTER SYSTEM DUMP DATAFILE 3 BLOCK 115; ---- (Data)
System altered
Look below at the segment header dumps, which will illustrate how the segment is stored.
Start dump data blocks tsn: 2 file#: 3 minblk 114 maxblk 114
buffer tsn: 2 rdba: 0x00c00072 (3/114)
scn: 0x0000.001fb61b seq: 0x02 flg: 0x00 tail: 0xb61b1002
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 space2: 0 #extents: 1 #blocks: 15
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00c00074 ext#: 0 blk#: 1 ext size: 15
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Disk Lock:: Locked by scn: 0x0001.01e.0000004b
Map Header:: next 0x00000000 #extents: 1 obj#: 3091 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c00073 length: 15
nfl = 1, nfb = 1 typ = 1 nxf = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 2 file#: 3 minblk 114 maxblk 114
Let's decode the segment header dump file.
Start dump data blocks tsn: 2 file#: 3 minblk 114 maxblk 114
buffer tsn: 2 rdba: 0x00c00072 (3/114)
scn: 0x0000.001fb61b seq: 0x02 flg: 0x00 tail: 0xb61b1002
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
The first data block in the segment is used to store the header information about that segment. In this case tsn is the tablespace number and file# is the (file_id in the dba_data_files) absolute file number. Minblk and maxblk have no impact since the header information was the only thing dumped.
The relative 'dba' is a relative data block address with respect to that data file. In this case, 3/114 is encoded rdba. The next is the 'scn' in the hex notation and the 'seq', which is the sequence number of the changes at the same scn. The 'tail' is the combination of the 'scn' and the 'seq'. It is used to keep track of consistency information between the beginning and end of the block against the possibility of distribution of oracle blocks over multiple OS blocks. It consists of lower order 2 bytes of SCNBase, plus block type and seq. The 'frmt' tells whether it is Oracle7 block or Oracle8 and higher blocks. Frmt:0x02 represents the Oracle8 block and 0x01 represents the Oracle 7 block. The next components are 'chkva;' which is checksum written to the blocks when it is set. Type explains what type of the block it is (like table block, index leaf block or header block).
Below is the decoded extent control header.
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 space2: 0 #extents: 1 #blocks: 15
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00c00074 ext#: 0 blk#: 1 ext size: 15
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Disk Lock:: Locked by scn: 0x0001.01e.0000004b
Map Header:: next 0x00000000 #extents: 1 obj#: 3091 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c00073 length: 15
nfl = 1, nfb = 1 typ = 1 nxf = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 2 file#: 3 minblk 114 maxblk 114
The above dump (Extent Control Header) illustrates that the table has only one extent and that 15 blocks are allocated for that extent. Block X has a high water mark that generally moves in multiples of 5 blocks. This number can be controlled by the undocumented parameter '_bump_highwatermark_count', which otherwise defaults to 5 data blocks. The blocks, from the high water mark down, will be linked to the free list chain unless the blocks are full or are not eligible for insert.
And now the data block dump.
Start dump data blocks tsn: 2 file#: 3 minblk 115 maxblk 115
buffer tsn: 2 rdba: 0x00c00073 (3/115)
scn: 0x0000.001fb61b seq: 0x02 flg: 0x00 tail: 0xb61b0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x00c00073
Object id on Block? Y
seg/obj: 0xc13 csc: 0x00.1fb619 itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0001.031.0000004b uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump
===============
tsiz: 0x1fb8
hsiz: 0x1a
pbl: 0x05e9c044
bdba: 0x00c00073
flag=-----------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f5c
avsp=0x1f42
tosp=0x1f42
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f9e
0x14:pri[1] offs=0x1f88
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f5c
block_row_dump:
tab 0, row 0, @0x1f9e
tl: 26 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f88
tl: 22 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 15
col 1: [ 8] 52 45 53 45 41 52 43 48
col 2: [ 6] 44 41 4c 4c 41 53
tab 0, row 2, @0x1f74
tl: 20 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 1f
col 1: [ 5] 53 41 4c 45 53
col 2: [ 7] 43 48 49 43 41 47 4f
tab 0, row 3, @0x1f5c
tl: 24 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [ 6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 115 maxblk 115
########### CACHE LAYER COMPONENTS ###########
### SQL> SELECT * FROM V$TYPE_SIZE
### 2 WHERE V$TYPE_SIZE.TYPE='KCBH';
###
### COMPONEN TYPE DESCRIPTION TYPE_SIZE
### -------- -------- -------------------------------- ----------
### KCB KCBH BLOCK COMMON HEADER 20
###
### TYPE=KCBH SIZE=20 BYTES
###
### SQL> SELECT *FROM v$TYPE_SIZE
### 2 WHERE V$TYPE_SIZE.TYPE='KSCN';
###
### COMPONEN TYPE DESCRIPTION TYPE_SIZE
### -------- -------- -------------------------------- ----------
### K KSCN SYSTEM COMMIT NUMBER 8
### TPYE=KSCN SIZE=8 BYTES
Start dump data blocks tsn: 2 file#: 3 minblk 115 maxblk 115
buffer tsn: 2 rdba: 0x00c00073 (3/115)
scn: 0x0000.001fb61b seq: 0x02 flg: 0x00 tail: 0xb61b0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
The Cache layer in the data block controls the buffer cache related information used in the read consistency. It has the System Commit Number information for that block.
########### TRANSACTION LAYER COMPONENTS (FIXED) ###########
### SQL> SELECT * FROM V$TYPE_SIZE
### 2 WHERE V$TYPE_SIZE.TYPE='KTBBH';
###
### COMPONEN TYPE DESCRIPTION TYPE_SIZE
### -------- -------- -------------------------------- ----------
### KTB KTBBH TRANSACTION FIXED HEADER 48
###
### TYPE=KTBBH SIZE=48 BYTES
Block header dump: 0x00c00073
Object id on Block? Y
seg/obj: 0xc13 csc: 0x00.1fb619 itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
########### TRANSACTION LAYER (VARIABLE) ###########
### SQL> SELECT * FROM V$TYPE_SIZE
### 2 WHERE V$TYPE_SIZE.TYPE='KTBIT';
###
### COMPONEN TYPE DESCRIPTION TYPE_SIZE
### -------- -------- -------------------------------- ----------
### KTB KTBIT TRANSACTION VARIABLE HEADER 24
###
### TYPE=KTBIT SIZE=24 BYTES (EACH ITL SIZE)
### CONTROLED BY THE PARAMETER INITRANS
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0001.031.0000004b uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
########### DATA LAYER HEADER ###########
### SQL> SELECT * FROM V$TYPE_SIZE
### 2 WHERE V$TYPE_SIZE.TYPE='KDBH';
###
### COMPONEN TYPE DESCRIPTION TYPE_SIZE
### -------- -------- -------------------------------- ----------
### KDB KDBH DATA HEADER 14
###
### TYPE=KDBH SIZE=14 BYTES
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f5c
avsp=0x1f42
tosp=0x1f42
########### TABLE DIRECTORY ###########
### SQL> SELECT * FROM V$TYPE_SIZE
### 2 WHERE V$TYPE_SIZE.TYPE='KDBT';
###
### COMPONEN TYPE DESCRIPTION TYPE_SIZE
### -------- -------- -------------------------------- ----------
### KDB KDBT TABLE DIRECTORY ENTRY 4
###
### TYPE=KDBT SIZE=4 BYTES
0xe:pti[0] nrow=3 offs=0
0xe:pti[0] nrow=4 offs=0
############## ROW DIRECTORY#############
0x12:pri[0] offs=0x1f9e
0x14:pri[1] offs=0x1f88
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f5c
block_row_dump:
tab 0, row 0, @0x1f9e
######### ROW DATA #############
block_row_dump:
tab 0, row 0, @0x1f9e
tl: 26 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f88
tl: 22 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 15
col 1: [ 8] 52 45 53 45 41 52 43 48
col 2: [ 6] 44 41 4c 4c 41 53
tab 0, row 2, @0x1f74
tl: 20 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 1f
col 1: [ 5] 53 41 4c 45 53
col 2: [ 7] 43 48 49 43 41 47 4f
tab 0, row 3, @0x1f5c
tl: 24 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [ 6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 115 maxblk 115