SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2(5)) tablespace test;
Table created.
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';
FILE_ID
----------
FILE_NAME
-----------------------------------------------------------------
5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF
SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST
_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
73512 73512
SQL> insert into test_table values (1,'AAAAA','BBB');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test_table values (2,'CCCCC','DDDDD');
1 row created.
SQL> commit;
Commit complete.
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segme
nt_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 136 8 5
SQL> alter system checkpoint;
System altered.
The table we are working on for this test is stored in file 5 with extent starting at block 136 in blocks with DATA_OBJECT_ID 73512 . When we scan the file for blocks with data object ID 73512 we can see that the rows we are interested in are in block 139. Two ITL Slots have been created in the block.
The following block data was extracted using :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
Block 139 Contains Object ID 73512
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971659
SCN Base :- 1072047
SCN Wrap :- 0
Sequence :- 02
Flag 01-NEW :- 06
CheckSum :- 17170
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73512
Cleanout SCN Base :- 1072042
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971656
ITLSlot :- 1
Undo Segment :- 10
Undo Segment Slot :- 29
Transaction Sequence :- 633
Undo Block Address :- 12583185
Undo Sequence :- 140
Undo Record Number :- 3
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 0
Base :- 1072043
ITLSlot :- 2
Undo Segment :- 5
Undo Segment Slot :- 19
Transaction Sequence :- 814
Undo Block Address :- 12583108
Undo Sequence :- 169
Undo Record Number :- 33
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 0
Base :- 1072047
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 02
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Now we update one record in the table.
SQL> update test_table set attribute1 = 'EEEEE' where attribute1 = 'AAAAA';
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
After dumping the block we can see that :-
1. block SCNs were updated
2. block CheckSum was updated
3. ITL Slot 1 was used for the transaction
4. ITL Slot 2 flag was updated to -32768 (I guess indicating unused)
5. The Row lock status for the untouched row was cleared.
6. The data was updated.
Block 139 Contains Object ID 73512
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971659
SCN Base :- 1072247
SCN Wrap :- 0
Sequence :- 02
Flag 01-NEW :- 06
CheckSum :- -6509
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73512
Cleanout SCN Base :- 1072246
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971656
ITLSlot :- 1
Undo Segment :- 4
Undo Segment Slot :- 16
Transaction Sequence :- 596
Undo Block Address :- 12583572
Undo Sequence :- 147
Undo Record Number :- 55
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 0
Base :- 1072247
ITLSlot :- 2
Undo Segment :- 5
Undo Segment Slot :- 19
Transaction Sequence :- 814
Undo Block Address :- 12583108
Undo Sequence :- 169
Undo Record Number :- 33
Spare 1 :- 0
Flag :- -32768
_ktbitun :- 0
Base :- 1072047
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- EEEEE
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 00
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
No comments:
Post a Comment