表中的數據被delete之後並不會真正刪除數據，而是打了一個刪除標記，只要還沒有被覆蓋就可以恢復回來。
實驗過程如下：
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
查看dump文件
block_row_dump:
tab 0, row 0, @0x1f96
tl: 10 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 6] 42 41 44 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
紅色標記的位置記錄了該行數據的狀態:
沒有被刪除的話是--H-FL--,其對應的值為0x2c
已經被刪除的話是--HDFL--，其對應的值為0x3c
下面我們刪除一行數據來看一下：
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.
查看dump文件
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
可以看到此時row 0的數據已經被標記為刪除。
下面使用bbed工具將該條數據恢復回來：
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
現在去庫裡查看一下
SYS@ORCL>alter system flush buffer_cache;
System altered.
SYS@ORCL>select * from bbed_test;
X
--------------------
BADLY9
JP
可以看到被delete的數據已經恢復回來。
