今天有朋友咨询,truncate table有drop storage和reuse storage方式,是否影响后续的数据恢复(在没有其他覆盖的情况下),我做了一个简单的测试证明,这些都不影响truncate table的数据库恢复
创建测试环境

SQL> create table t_xifenfei tablespace USERNEW
  2   as select * from dba_objects;
Table created.
SQL> create table t_xifenfei2  tablespace USERNEW
  2   as select * from dba_objects;
Table created.
SQL>  create table t_xifenfei3  tablespace USERNEW
  2  as select * from dba_objects;
Table created.
SQL> alter system checkpoint;
System altered.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     86348
SQL>  select count(*) from t_xifenfei2;
  COUNT(*)
----------
     86349
SQL>  select count(*) from t_xifenfei3;
  COUNT(*)
----------
     86350
SQL> select object_id,data_object_id,object_name from dba_objects where object_name like 't_xifenfei%';
 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
     88205          88205 t_xifenfei
     88206          88206 t_xifenfei2
     88207          88207 t_xifenfei3
SQL> truncate table t_xifenfei;
Table truncated.
SQL> truncate table t_xifenfei2 drop storage;
Table truncated.
SQL> truncate table t_xifenfei3 reuse storage;
Table truncated.

使用dul进行恢复

DUL>  bootstrap;
DUL> scan database;
scanning database...
scanning database finished.
DUL> unload table sys.t_xifenfei object 88205;
Unloading table: t_xifenfei,object ID: 88205
Unloading segment,storage(Obj#=88205 DataObj#=88205 TS#=6 File#=5 Block#=1410 Cluster=0)
86348 rows unloaded
DUL> unload table sys.t_xifenfei2 object 88206;
Unloading table: t_xifenfei2,object ID: 88206
Unloading segment,storage(Obj#=88206 DataObj#=88206 TS#=6 File#=5 Block#=2690 Cluster=0)
86349 rows unloaded
DUL> unload table sys.t_xifenfei3 object 88207;
Unloading table: t_xifenfei3,object ID: 88207
Unloading segment,storage(Obj#=88207 DataObj#=88207 TS#=6 File#=5 Block#=3970 Cluster=0)
86350 rows unloaded

这里证明truncate table不管是drop storage还是reuse storage或者默认,在没有被覆盖的情况下，数据均完全恢复出来