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
No comments:
Post a Comment