ORACLE space management experiment 8: data block format Analysis

Use DUMP data blocks in combination with BBED for viewing. ################## Test preparation steps: BYS @ bys3createtabletest6 (aaint, bbvarchar2 (10); Tablecreated. BYS @ bys3insertintotest6values (89, bys); 1rowcreated. BYS @ bys3insertintotest6values (69, h

Use DUMP data blocks in combination with BBED for viewing. ################## Test preparation procedure: BYS @ bys3create table test6 (aa int, bb varchar2 (10 )); table created. BYS @ bys3insert into test6 values (89, 'bys '); 1 row created. BYS @ bys3insert into test6 values (69, 'H

Use the DUMP data block format in combination with BBED for viewing.
#################### Lab preparation steps:
BYS @ bys3> create table test6 (aa int, bb varchar2 (10 ));
Table created.
BYS @ bys3> insert into test6 values (89, 'bys ');
1 row created.
BYS @ bys3> insert into test6 values (69, 'Hello ');
1 row created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> select dbms_rowid.rowid_relative_fno (rowid) file #, dbms_rowid.rowid_block_number (rowid) block #, aa, bb from test6;
FILE # BLOCK # AA BB
----------------------------------------
4 477 89 bys
4 477 69 hello
BYS @ bys3> alter system dump datafile 4 block 477;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------

/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_8109.trc

DUMP data block information

Start dump data blocks tsn: 4 file #: 4 minblk 477 maxblk 477
Block dump from cache: -- this section of information comes from the buffer cache. For details, see Buffer Header -- DUMP buffer combined with fields in the X $ BH view.
Dump of buffer cache at level 4 for tsn = 4 rdbas = 16777693
BH (0x22be4e74) file #: 4 rdba: 0x010001dd (4/477) class: 1 ba: 0x2286e000
Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 0
Dbwrid: 0 obj: 23326 objn: 23326 tsn: 4 afn: 4 hint: f
Hash: [0x227e6b54, 0x2a7f74ac] lru: [0x217ee3d4, 0x20ff4e64]
Ckptq: [NULL] fileq: [NULL] objq: [0x217ee3ec, 0x20ff4e7c] objaq: [0x217ee3f4, 0x20ff4e84]
St: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb 'tch: 3
Flags: block_written_once redo_since_read
LRBA: [0x0. 0.0] LSCN: [0x0.0] HSCN: [0xffff. ffffffff] HSUB: [1]
######################################## ### Data block Header
Block dump from disk: -- The following information is from the Block in the data file.
Buffer tsn: 4 rdba: 0x010001dd (4/477) -- 4-8 bytes in the data block are RDBA -- The following BBED section can be seen
Scn: 0x0000. 00874dbb seq: 0x01 flg: 0x06 tail: 0x4dbb0601
Frmt: 0x02 chkval: 0xeb56 type: 0x06 = trans data -- the fourth byte corresponds
--- Flg: 0x01 (new block) 0x2 (data block delay cleaning advances scn and seq) 0X04 (set checksum) 0x08 (temporary block) type: 0x06 (Table/index block)
-- Frmt: 0x01 (v7) 0x02 (v8) -- corresponds to the third byte A2, indicating 8I or above
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB68A9200 to 0xB68AB200
B68A9200 g0a206 010001DD 00874DBB 06010000 [...] --- the information of this row can correspond to the scn type in the block header.
B68A9210 425eb56 00130001 4255b1e 00874DB6 [V ......]
B68A9220 1FE80000 00321F02 010001D8 001A0002 [...... 2 ......]
B68A9230 00001382 00C00B70 00070569 00002002 [...... p ...... I ......]
....................................
B68AB1D0 54415453 4D5F5355 454B5241 71780752 [STATUS_MARKER.xq]
B68AB1E0 2618100B 012C021E 46C10202 6C656805 [......, ...... F. hel]
B68AB1F0 012C6F6C 5AC10202 73796203 4DBB0601 [lo,... Z. bys... M]
######################################## # ITL
Block header dump: 0x010001dd
Object id on Block? Y
Seg/obj: 0x5b1e csc: 0x00. 874db6 itc: 2 flg: E typ: 1-DATA -- the DATA type is DATA.
-- Seg/obj: 0x5b1e -- corresponding to dba_objects.data_object_id. The data_object_id and object_id of a table that has not been TRUNCATE. Format, that is, write the seg/obj on the block:
-- Csc: SCN for block clearing when 0x00. 874db6 is delayed -- at query time and at the third commit time -- three ITL will do delayed block clearing
-- Flg: E -- indicates that ASSM is used. If it is O, free list is used.
-- Typ: 1-DATA transaction-type DATA block (and: type of the DATA block header: 0x06), which stores table and index DATA.
Brn: 0 bdba: 0x10001d8 ver: 0x01 opc: 0
Inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002. 01a. 00001382 0x00c00b70. 0569.07 -- U-2 fsc 0x0000. 00874dbb
0x02 0x0000. 000.00000000 0x00000000. 0000.00 ---- 0 fsc 0x0000.00000000
-- 11G uses quick submission by default. Flag is U, and normal submission is C.
-- Itl: the flow number of the ITL transaction slot.
-- Xid: transac [X] tion identified (transaction ID), which consists of the field number of und, the slot number of undo, and the number of overwrites of the undo slot number.
-- Uba: undo block address records the previous image of the last record (the value before modification)
-- Flag: C Indicates submission, U indicates fast submission, and --- indicates not submitted (Flg C = Committed U = Commit Upper Bound T = Active at CSC)
-- Lck: several rows of data are locked, and several rows of data should be locked.
-- Scn/Fsc: Scn = SCN of commited TX; Fsc = Free space credit (bytes)
-- Here, fsc 0x0000. 00874dbb refers to the committed scn. This value is greater than the scn = csc: 0x00. 874db6 when the block was last cleared (this scn is the smallest SCN of commited in this block)
-- Scn wrap: if the transaction has been committed and cleaned, this field stores the scn wrap part of the transaction submitted. Otherwise, this field stores the number of idle advance bytes (FSC ). for example, if a row of data is deleted with 10 bytes, the 10 bytes before the transaction advances belong to fsc (that is, the 10 bytes will be written to scn wrap). Only after the transaction is committed, to return to the idle space.

######################################## ######## User data Header
Bdba: 0x010001dd -- DBA of the current data block
Data_block_dump, data header at 0xb68a9264
====================
Tsiz: total available space of 0x1f98 blocks 1f98 -- 8088 bytes
Hsiz: 0x16 -- number of bytes occupied by the Data header-unfixed
Pbl: 0xb68a9264
76543210
Flag = --------
Ntab = 1 -- the data block belongs to a table, and the cluster table is not 1
Nrow = 2 -- number of rows
Frre =-1 -- The first free row entry in the row directory needs to add 1
Fsbo = 0x16 -- Free space begin offset is called the starting space: the starting position where the data space can be stored (that is, the starting offset of the Free space in the data layer is defined)
Fseo = 0x1f82 -- Free space end offset is called the end space: the end location of the data space that can be stored (that is, the end offset of the idle space in the data layer is defined)Insert data from this point-use from the back to the front
Avsp = 0x1f6c -- Available space for new entries is called Free space: defines the number of bytes of free space in the data layer.
Tosp = 0x1f6c -- Total space is called the final idle space: defines the number of bytes of free space in the data layer after the transaction commit in ITL
0xe: pti [0] nrow = 2 offs = 0 -- Table directory, the beginning of the entire Table, a total of 2 rows of data, defines the number of slots used by the Table in the row Index
0x12: pri [0] offs = 0x1f8e-Row index, which defines the location of all Row data contained in this block.
0x14: pri [1] offs = 0x1f82

####################################### User data
Block_row_dump:
Tab 0, row 0, @ 0x1f8e -- 1 table, row 1st, @ 0x1f8e starting slot number 8078 in the row Index
Tl: 10 fb: -- H-FL -- lb: 0x1 cc: 2
-- Fb: (Flag byte) -- H-FL refers to H (Head piece of row) F (First data piece) L (Last data piece)
-- Lb: 0x1 -- Lock byte corresponds to the lck of the ITL, indicating whether the row is locked.
Col 0: [2] c1 5a -- the first column in the first row, with two characters
Col 1: [3] 62 79 73 -- the second column of the first row, with three characters
Tab 0, row 1, @ 0x1f82---------- Use this to convert to decimal to view in BBED at this offset. You need to add 100 (100 bytes reserved by El)
Tl: 12 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 46
Col 1: [5] 68 65 6c 6c 6f
End_of_block_dump
End dump data blocks tsn: 4 file #: 4 minblk 477 maxblk 477
The last four bytes of tail: 0xa3eb0601 = scnBASE + flg + seq. If they are not equal, block corruption is reported.
###################

Use BBED to view the data block, which corresponds to the DUMP information in the previous step.

To save space, haha, BBED only talks about the correspondence with DUMP and the significance of some important fields. If it is not important, you should look at DUMP in the previous step.
##########################
BBED> set file 4 block 477
FILE #4
BLOCK #477.
BBED> dump
File:/u01/oradata/bys3/user01.dbf (4)
Block: 477 Offsets: 0 to 511 Dba: 0x010001dd
------------------------------------------------------------------------
06a20000 dd010001 bb4d8700 00000106 56eb0000 01001300 1e5b0000 b64d8700
From the first line of BBED information, because of the size of the end of the problem, here we need to look at the two sides.
In hexadecimal notation, two characters are 1 bytes. Therefore, two hexadecimal characters (1 byte) must be used for conversion:
The first byte is 0000 a206 0100 01dd. It can be seen that the first eight bytes of the DUMP data block are the same,
#####
BBED> map
File:/u01/oradata/bys3/user01.dbf (4)
Block: 477 Dba: 0x010001dd
------------------------------------------------------------
KTB Data Block (Table/Cluster)
Struct kcbh, 20 bytes @ 0
Struct ktbbh, 72 bytes @ 20
Struct kdbh, 14 bytes @ 100
Struct kdbt [1], 4 bytes @ 114
Sb2 kdbr [2] @ 118
Ub1 freespace [8044] @ 122
Ub1 rowdata [22] @ 8166
Ub4 tailchk @ 8188

############################# 3
BBED> print kcbh --- all the information here can correspond to the information in DUMP. Corresponding to the cache layer in the figure
Struct kcbh, 20 bytes @ 0
Ub1 type_kcbh @ 0 0x06 -- block type .... Ub4 -- indicates: unsign bytes 4 -- indicates the number of bytes.
Ub1 frmt_kcbh @ 1 0xa2 -- version 8I or above
Ub1 sparejavaskcbh @ 2 0x00
Ub1 spare2_kcbh @ 3 0x00
Ub4 rdba_kcbh @ 4 0x010001dd-DBA
Ub4 bas_kcbh @ 8 0x00874dbb-SCN low position
Ub2 wrp_kcbh @ 12 0x0000-SCN high
Ub1 seq_kcbh @ 14 0x01 -- serial number
Ub1 flg_kcbh @ 15 0x06 (KCBHFDLC, KCBHFCKV)
Ub2 chkval_kcbh @ 16 0xeb56 -- chkval in DUMP
Ub2 spare3_kcbh @ 18 0x0000


BBED>Print ktbbh --- corresponds to ITL transaction information
Struct ktbbh, 72 bytes @ 20
Ub1 ktbbhtyp @ 20 0x01 (KDDBTDATA) -- block type
Union ktbbhsid, 4 bytes @ 24 --- seg/obj: 0x5b1e
Ub4 ktbbhsg1 @ 24 0x00005b1e
Ub4 ktbbhod1 @ 24 0x00005b1e
Struct ktbbhcsc, 8 bytes @ 28 -- csc: 0x00. 874db6
Ub4 kscnbas @ 28 0x00874db6
Ub2 kscnwrp @ 32 0x0000
Sb2 ktbbhict @ 36 7938 -- itc: 2 I am not correct here
Ub1 ktbbhflg @ 38 0x32 (NONE) -- flg: E
Ub1 ktbbhfsl @ 39 0x00
Ub4 ktbbhfnx @ 40 0x010001d8 -- bdba:
Struct ktbbhitl [0], 24 bytes @ 44 -- corresponding transaction No. Xid: 0x0002. 01a. 00001382
Struct ktbitxid, 8 bytes @ 44
Ub2 kxidusn @ 44 0 0x0002-usn undo segment number
Ub2 kxidslt @ 46 0x001a -- number of rows in the transaction table
Ub4 kxidsqn @ 48 0x00001382 -- number of times the row is reused
Struct ktbituba, 8 bytes @ 52 -- transaction UBA 0x00c00b70. 0569.07
Ub4 kubadba @ 52 0x00c00b70 -- UNDO DBA
Ub2 kubaseq @ 56 0x0569 --
Ub1 kubarec @ 58 0x07
Ub2 ktbitflg @ 60 0x2002 (KTBFUPB)
Union _ ktbitun, 2 bytes @ 62
Sb2 _ ktbitfsc @ 62 0
Ub2 _ ktbitwrp @ 62 0x0000
Ub4 ktbitbas @ 64 0x00874dbb
Struct ktbbhitl [1], 24 bytes @ 68
Struct ktbitxid, 8 bytes @ 68
Ub2 kxidusn @ 68 0x0000
Ub2 kxidserver Load balancer @ 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 zero x 0000 (NONE)
Union _ ktbitun, 2 bytes @ 86
Sb2 _ ktbitfsc @ 86 0
Ub2 _ ktbitwrp @ 86 0x0000
Ub4 ktbitbas @ 88 0x00000000

BBED> print kdbh -- corresponding user data Header
Struct kdbh, 14 bytes @ 100
Ub1 kdbhflag @ 100 0x00 (NONE)
Sb1 kdbhntab @ 101 1 -- corresponding DUMP: ntab = 1
Sb2 kdbhnrow @ 102 2 -- corresponding to DUMP: nrow = 2
Sb2 kdbhfrre @ 104-1 -- corresponding to the DUMP: frre =-1
Sb2 kdbhfsbo @ 106 22 -- corresponding to DUMP: fsbo = 0x16
Sb2 kdbhfseo @ 108 8066 -- corresponding DUMP: fseo = 0x1f82 data insertion starts from this
Sb2 kdbhavsp @ 110 8044 -- corresponding to avsp = 0x1f6c in DUMP
Sb2 kdbhtosp @ 112 8044 -- corresponding to tosp = 0x1f6c in DUMP

BBED> print kdbr -- corresponding row index information
Sb2 kdbr [0] @ 118 8078 -- corresponding to 0x12: pri [0] offs = 0x1f8e in DUMP
Sb2 kdbr [1] @ 120 8066 -- corresponding to 0x14: pri [1] offs = 0x1f82 in DUMP
##########
BBED> dump offset 8166 -- the specific information in the row is dumped here.First line 8078 second line 8066 plus 100, starting from 8166 DUMP
File:/u01/oradata/bys3/user01.dbf (4)
Block: 477 Offsets: 8166 to 8191 Dba: 0x010001dd
------------------------------------------------------------------------
2c010202 c1460568 656c6c6f 2c010202 c15a021379730106 bb4d
02 c15a 03 62 7973 the value of the first line: 03 is three bytes,
Col 0: [2] c1 5a
Col 1: [3] 62 79 73

02 c14605 68 656c6c6f corresponds to the value of the second row: 05 is five bytes
Col 0: [2] c1 46
Col 1: [5] 68 65 6c 6c 6f

BBED> print tailchk -- corresponds to 4DBB0601 in the last four bytes of the data block in DUMP, which is the check value of the data block.
Ub4 tailchk @ 8188 0x4dbb0601