Monday, February 25, 2013

What happens in an Oracle Block during an update.

The following is an investigation of what happens in an Oracle datafile block during an update.  First we create a simple table with two rows.


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