Sundaramurthy Blog

June 18, 2010

Oracle Data Block Internals (DBA 4,31)

Filed under: Oracle internals — sundar5 @ 2:22 pm

When you create a table object on a tablespace, oracle creates the segment header block, BMB (Bit Map Block) block and data blocks in the data file.  Below is the data block internal structure  where the input data gets inserted.

For example, I have created the  following table structure and inserted the record.  See the record in the data block.

SQL> CREATE TABLE T1 (N1 number, desc1 varchar2(10))  TABLESPACE POCTABLESPACE;

SQL> SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE FROM DBA_OBJECTS

WHERE OBJECT_NAME=’T1′ AND OWNER=’SYS’

OBJECT_NAM  OBJECT_ID OBJECT_TYPE

———- ———- ————-

T1               9858 TABLE

SQL> INSERT INTO T1 VALUES (100,’TESTDATA’);

1 row created.
SQL> COMMIT;
SQL> select * from t1;
N1 DESC1
---------- ----------
100 TESTDATA

Let see how oracle internally store the data in hex format:

SQL> select dump(100,16),dump(‘TESTDATA’,16) FROM DUAL;

—————– ————————————-

Typ=2 Len=2: c2,2 Typ=96 Len=8: 54,45,53,54,44,41,54,41

Transaction data Block Internal Structure:

Block header
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x0100001f
ub4 bas_kcbh @8 0x00054f17
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x6fc8
ub2 spare3_kcbh @18 0x0000
Transaction Layer
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00002682
ub4 ktbbhod1 @24 0x00002682
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x00054f17
ub2 kscnwrp @32 0x0000
b2 ktbbhict @36 7938
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000009
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x000f
ub4 kxidsqn @48 0x000000ef
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00800318
ub2 kubaseq @56 0x0041
ub1 kubarec @58 0x22
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x00054ec3
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
Table Directory
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
b1 kdbhntab @101 1
b2 kdbhnrow @102 1
Free List
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 20
sb2 kdbhfseo @108 8073
b2 kdbhavsp @110 8053
b2 kdbhtosp @112 8053
Row Directory
sb2 kdbr[0] @118 8073
rowdata[0]
———-
ub1 rowdata[0] @8173 0x2c
Row
rowdata[0] @8173
———-
flag@8173: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8174: 0x00
cols@8175:    2
col    0[2] @8176: 100
col    1[8] @8179: TESTDATA

Transaction Data block:

Legend:

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: