When the data in the table is deleted, it does not actually delete the data, but instead it makes a deletion mark that can be recovered as long as it is not overwritten.
The experimental process is as follows:
Sys@orcl>create table Bbed_test (x varchar2 (20));
Table created.
Sys@orcl>insert into bbed_test values (' BADLY9 ');
1 row created.
Sys@orcl>insert into bbed_test values (' JP ');
1 row created.
sys@orcl>commit;
Commit complete.
Sys@orcl>select rowID, Dbms_rowid.rowid_relative_fno (ROWID) Rel_fno,
2 Dbms_rowid.rowid_block_number (ROWID) Blockno,
3 Dbms_rowid.rowid_row_number (ROWID) rowno
4 from Bbed_test;
ROWID rel_fno Blockno Rowno
------------------ ---------- ---------- ----------
AAAM0VAABAAAOUCAAA 1 60290 0
Aaam0vaabaaaoucaab 1 60290 1
Sys@orcl>alter system flush Buffer_cache;
System altered.
Sys@orcl>alter System dump datafile 1 block 60290;
System altered.
Sys@orcl>oradebug Setmypid
Statement processed.
Sys@orcl>oradebug Tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_18509.trc
View dump File
Block_row_dump:
tab 0, row 0, @0x1f96
Tl:10 FB:--h-fl--lb:0x1 cc:1
Col 0: [6] 4c 59 39
tab 0, row 1, @0x1f90
Tl:6 FB:--h-fl--lb:0x1 cc:1
Col 0: [2] 4a 50
End_of_block_dump
The position of the red Mark records the status of the row data:
This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/
If it's not deleted, it's--H-FL--, its corresponding value is 0x2c
The words that have been deleted are--hdfl--, and their corresponding values are 0x3c
Now let's delete one line of data to see:
Sys@orcl>delete bbed_test where x= ' BADLY9 ';
1 row deleted.
sys@orcl>commit;
Commit complete.
Sys@orcl>select * from Bbed_test;
X
--------------------
Jp
Sys@orcl> alter system flush Buffer_cache;
System altered.
Sys@orcl>alter System dump datafile 1 block 60290;
System altered.
View dump File
Block_row_dump:
tab 0, row 0, @0x1f96
Tl:2 FB:--hdfl--lb:0x2
tab 0, row 1, @0x1f90
Tl:6 FB:--h-fl--lb:0x0 cc:1
Col 0: [2] 4a 50
End_of_block_dump
You can see that row 0 data has been marked for deletion at this time.
The following use the Bbed tool to restore this data back:
Bbed> Set DBA 1,60290
DBA 0x0040eb82 (4254594 1,60290)
Bbed> f/c BADLY9
File:/u01/app/oracle/oradata/orcl/system01.dbf (1)
block:60290 offsets:8182 to 8191 dba:0x0040eb82
------------------------------------------------------------------------
4241444c 59390206 1e62
<32 bytes per line>
Bbed> P *KDBR
ROWDATA[6]
----------
Ub1 Rowdata[6] @8178 0x3c
Bbed> Set Offset 8178
OFFSET 8178
Bbed> m/x 2c
File:/u01/app/oracle/oradata/orcl/system01.dbf (1)
block:60290 offsets:8178 to 8191 dba:0x0040eb82
------------------------------------------------------------------------
2c020106 4241444c 59390206 1e62
<32 bytes per line>
bbed> sum apply
Check value for File 1, block 60290:
Current = 0xc7d4, required = 0xc7d4
Now check it out in the library.
Sys@orcl>alter system flush Buffer_cache;
System altered.
Sys@orcl>select * from Bbed_test;
X
--------------------
BADLY9
Jp
You can see that the data that was delete has been restored back.