mysql drop storage_truncate table 无论drop storage还是reuse storage不影响数据恢复

今天有朋友咨询,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或者默认,在没有被覆盖的情况下，数据均完全恢复出来