有朋友在MSN上问我"truncate或drop后一定可以恢复数据吗？"
我回答说只要数据没有被覆盖，那么就一定可以。
他接着问"那如果这个表里有脏块怎么办？按我的理解，如果有脏块的话，这些脏块在truncate或者drop后是会丢失的。"
其实这位朋友所担心的问题并不会发生，因为oracle在做truncate或者drop的时候会触发object checkpoint。
我们来看一个实例：
SQL_testdb>create table test_truncate_recovery as select * from dba_users where 1=2;
Table created.
SQL_testdb>insert into test_truncate_recovery select * from dba_users;
29 rows created.
SQL_testdb>commit;
Commit complete.
SQL_testdb>select t.username,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from test_truncate_recovery t;
USERNAME LOCATION
------------------------------ ---------------------------------------------------------------------------------
SYS 1_53498
SYSTEM 1_53498
......省略显示部分内容
QS_CS 1_53498
29 rows selected.
从结果里我们可以看到现在表test_truncate_recovery里有29条记录，且第一条记录（即username为SYS的那条记录）所在的物理地址为file 1，block 53498。
我们去看一下这29条记录所在的block：
BBED> set file 1
FILE# 1
BBED> set block 53498
BLOCK# 53498
BBED> map /v
File: /dras20/testdb/system01.dbf (1)
Block: 53498 Dba:0x0040d0fa
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> dump
File: /dras20/testdb/system01.dbf (1)
Block: 53498 Offsets: 0 to 511 Dba:0x0040d0fa
------------------------------------------------------------------------
00020000 0040d0fa 00000000 00000105 d1bc0000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
......省略显示部分内容
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
从结果里我们可以看到这29条记录还没有被写回到datafile里，也就是说这29条记录现在还都在脏块里。
现在我们来对表test_truncate_recovery做truncate操作：
SQL_testdb>truncate table test_truncate_recovery;
Table truncated.
执行完后再去看一下这29条记录所在的block：
BBED> set file 1
FILE# 1
BBED> set block 53498
BLOCK# 53498
BBED> map
File: /dras20/testdb/system01.dbf (1)
Block: 53498 Dba:0x0040d0fa
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[29] @110
ub1 freespace[4881] @168
ub1 rowdata[3139] @5049
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @5049 0x
BBED> x /rcncc
rowdata[0] @5049
----------
flag@5049: 0x
lock@5050: 0x01
cols@5051: 11
col 0[3] @5052: SYS
col 1[1] @5056: 0
col 2[16] @5058:
col 3[4] @5075: OPEN
col 4[0] @5080: *NULL*
col 5[0] @5081: *NULL*
col 6[6] @5082: SYSTEM
col 7[4] @5089: TEMP
col 8[7] @5094: xf....#
col 9[7] @5102: DEFAULT
col 10[9] @5110: SYS_GROUP
从结果里我们可以看到在执行了truncate操作后，刚才那29条脏数据已经被写回到了datafile里。
如法炮制，可以测一下drop后的情况：
SQL_testdb>create table test_drop_recovery as select * from dba_users where 1=2;
Table created.
SQL_testdb>insert into test_drop_recovery select * from dba_users;
29 rows created.
SQL_testdb>commit;
Commit complete.
SQL_testdb>select t.username,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from test_drop_recovery t;
USERNAME LOCATION
------------------------------ ---------------------------------------------------------------------------------
SYS 1_54018
SYSTEM 1_54018
......省略显示部分内容
QS_CS 1_54018
29 rows selected.
BBED> set file 1
FILE# 1
BBED> set block 54018
BLOCK# 54018
BBED> map /v
File: /dras20/testdb/system01.dbf (1)
Block: 54018 Dba:0x0040d302
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> dump
File: /dras20/testdb/system01.dbf (1)
Block: 54018 Offsets: 0 to 511 Dba:0x0040d302
------------------------------------------------------------------------
00020000 0040d302 00000000 00000105 d2440000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
......省略显示部分内容
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
SQL_testdb>drop table test_drop_recovery;
Table dropped.
BBED> set file 1
FILE# 1
BBED> set block 54018
BLOCK# 54018
BBED> map
File: /dras20/testdb/system01.dbf (1)
Block: 54018 Dba:0x0040d302
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[29] @110
ub1 freespace[4881] @168
ub1 rowdata[3139] @5049
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @5049 0x
BBED> x /rcncc
rowdata[0] @5049
----------
flag@5049: 0x
lock@5050: 0x01
cols@5051: 11
col 0[3] @5052: SYS
col 1[1] @5056: 0
col 2[16] @5058:
col 3[4] @5075: OPEN
col 4[0] @5080: *NULL*
col 5[0] @5081: *NULL*
col 6[6] @5082: SYSTEM
col 7[4] @5089: TEMP
col 8[7] @5094: xf....#
col 9[7] @5102: DEFAULT
col 10[9] @5110: SYS_GROUP
Leave a comment