创建模拟表数据
SQL> create table t_xifenfei(id number,name varchar2(10)); Table created. SQL> insert into t_xifenfei values(1,'xifenfei'); 1 row created. SQL> insert into t_xifenfei values(2,'XIFENFEI'); 1 row created. SQL> commit; Commit complete.
dump数据块
SQL> alter system flush BUFFER_CACHE; System altered. SQL> select rowid,id,name, 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from chf.t_xifenfei; ROWID ID NAME REL_FNO BLOCKNO ROWNO ------------------ ---------- ---------- ---------- ---------- ---------- AAASdmAAEAAAACvAAA 1 xifenfei 4 175 0 AAASdmAAEAAAACvAAB 2 XIFENFEI 4 175 1 SQL> alter system dump datafile 4 block 175; System altered.
dump文件内容
block_row_dump: tab 0, row 0, @0x1f89 tl: 15 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 8] 78 69 66 65 6e 66 65 69 tab 0, row 1, @0x1f7a tl: 15 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [ 8] 58 49 46 45 4e 46 45 49 end_of_block_dump 2012-05-01 05:09:29.287714 : kjbmbassert [0xaf.4] End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
删除表数据
SQL> delete from t_xifenfei; 2 rows deleted. SQL> commit; Commit complete. SQL> alter system flush BUFFER_CACHE; System altered. SQL> alter system dump datafile 4 block 175; System altered.
dump文件内容
block_row_dump: tab 0, row 0, @0x1f89 tl: 2 fb: --HDFL-- lb: 0x2 tab 0, row 1, @0x1f7a tl: 2 fb: --HDFL-- lb: 0x2 end_of_block_dump 2012-05-01 05:13:35.214357 : kjbmbassert [0xaf.4] End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
通过对比这两次的dump文件发现
1.数据内容被删除,并不是真正删除,而是给其增加了一个标识位(fd:---D----) 2.fb:--H-FL--(head of row piece+first data piece+last data piece ) 其有8个选项每个选项的值分别对应bitmask即32+8+4=44 or 0x2c 3.如果一个row被delete了,那么row flag就会更新,bitmask里的deleted被设置为16. 此时row flag为：32+16+8+4 = 60 or 0x3c. 4.如果我们要找回来被删除的数据,只需要把3c改为2c即可
关闭数据库
SQL> select * from chf.t_xifenfei; no rows selected SQL> select name from v$datafile where file#=4; NAME ------------------------------------------------ /tmp/user01.dbf SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
bbed修改数据
BBED> set filename '/tmp/user01.dbf'
FILENAME /tmp/user01.dbf
BBED> set block 175
BLOCK# 175
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set mode edit
MODE Edit
BBED> map
File: /tmp/user01.dbf (0)
Block: 175 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[2] @118
ub1 freespace[8036] @122
ub1 rowdata[30] @8158
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[15]
-----------
ub1 rowdata[15] @8173 0x3c
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0] @8158 0x3c
BBED> m /x 2c offset 8158
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8158 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1030858 4946454e 46454932 630202c1 02087869 66656e66 65690106
b47e
<32 bytes per line>
BBED> m /x 2c offset 8173
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8173 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1020878 6966656e 66656901 06b47e
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 175:
current = 0x4d13, required = 0x4d13
启动数据库验证
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1346140 bytes
Variable Size 411043236 bytes
Database Buffers 117440512 bytes
Redo Buffers 5832704 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xifenfei;
ID NAME
---------- ----------
1 xifenfei
2 XIFENFEI
