Monday, February 25, 2013

What happens in an Oracle block during a delete.

The following is an investigation of what happens in the Oracle database block when a record is deleted.  First we create a table with 2 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
---------- --------------
     73513          73513

SQL> insert into test_table values (1,'AAAAA','BBB');

1 row created.

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 segment_name = 'TEST_TABLE';

   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
         5        144          8            5

SQL> alter system checkpoint;

System altered.


When we scan the file for data object ID 73513 we find the data in block 150.  The following block data was extracted using :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html


Block 150 Contains Object ID 73513
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971670
 SCN Base                    :- 1073062
 SCN Wrap                    :- 0
 Sequence                    :- 01 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- 14473
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73513
 Cleanout SCN Base           :- 1073058
 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     :- 20971664
ITLSlot                     :- 1
 Undo Segment               :- 5
 Undo Segment Slot          :- 33
 Transaction Sequence       :- 814
 Undo Block Address         :- 12583110
 Undo Sequence              :- 169
 Undo Record Number         :- 6
 Spare 1                    :- 0
 Flag                       :- 8194
 _ktbitun                   :- 0
 Base                       :- 1073062
ITLSlot                     :- 2
 Undo Segment               :- 0
 Undo Segment Slot          :- 0
 Transaction Sequence       :- 0
 Undo Block Address         :- 0
 Undo Sequence              :- 0
 Undo Record Number         :- 0
 Spare 1                    :- 0
 Flag                       :- 0
 _ktbitun                   :- 0
 Base                       :- 0
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        :- 01 
 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 delete one row :-


SQL> delete from test_table where id = 1;

1 row deleted.

SQL> commit;

Commit complete.


SQL> select * from test_table;

        ID ATTRI ATTRI
---------- ----- -----
         2 CCCCC DDDDD


SQL> alter system checkpoint;

System altered.



And we can extract the block data again and confirm that the result of the delete is that  :-

1. the block SCNs and Sequence have been updated
2. the block CheckSum has been updated
3. ITL Slot 2 was used for the transaction
4. ITL Slot 1 flag was set to -32768 (unused)
5. The row header flag was updated from 2C to 3C
6. The row header lock status was updated to 02

Note that the delete did not actually remove the record from the block, it just updated the row flag.  This means that even if a record has been deleted from an Oracle database table is possible to read the data for the deleted record directly from the block (until it is overwritten).



Block 150 Contains Object ID 73513
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971670
 SCN Base                    :- 1073294
 SCN Wrap                    :- 0
 Sequence                    :- 02 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- -6419
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73513
 Cleanout SCN Base           :- 1073292
 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     :- 20971664
ITLSlot                     :- 1
 Undo Segment               :- 5
 Undo Segment Slot          :- 33
 Transaction Sequence       :- 814
 Undo Block Address         :- 12583110
 Undo Sequence              :- 169
 Undo Record Number         :- 6
 Spare 1                    :- 0
 Flag                       :- -32768
 _ktbitun                   :- 0
 Base                       :- 1073062
ITLSlot                     :- 2
 Undo Segment               :- 6
 Undo Segment Slot          :- 27
 Transaction Sequence       :- 797
 Undo Block Address         :- 12583068
 Undo Sequence              :- 174
 Undo Record Number         :- 43
 Spare 1                    :- 0
 Flag                       :- 8193
 _ktbitun                   :- 14
 Base                       :- 1073294
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              :-  3C  Deleted
 Lock Status        :- 02 
 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        :- 00 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD