oracle 11g drop table 后闪回

傷城~ 2022-08-08 09:15 176阅读 0赞

**--初始化数据**

drop table test purge;

create table test as select \* from dba\_objects;  
delete from test where object\_id is null;  
alter table test add constraint pk\_test\_object\_id primary key(object\_id);  
create index ind\_t\_object\_name on test(object\_name);  
  
**--执行删除操作**  
drop table test;  
  
select r.object\_name,r.original\_name,r.operation,r.type from recyclebin r;  
OBJECT\_NAME                     ORIGINAL\_NAME       OPERATION TYPE  
\------------------------------- ------------------ --------  --------  
BIN$FfQ1SQRoVJjgUAoKlg9C7A==$0  TEST                DROP      TABLE  
BIN$FfQ1SQRnVJjgUAoKlg9C7A==$0  PK\_TEST\_OBJECT\_ID   DROP      INDEX  
BIN$FfQ1SQRmVJjgUAoKlg9C7A==$0  IND\_T\_OBJECT\_NAME   DROP      INDEX  
  
**--生成闪回表的语句**  
select 'flashback table ' || r.original\_name || ' to before drop;' cc  
  from recyclebin r  
 where type = 'TABLE';  
CC  
\--------------------------------------  
flashback table TEST to before drop;  
   
**--索引会被闪回，但名称会被改 **  
select 'alter index  "' || r.object\_name || '" rename to "' ||   
       r.original\_name || '" ;' ccc  
  from recyclebin r  
 where type = 'INDEX';  
CCC  
\---------------------------------------------------------------------------------  
alter index  "BIN$FfQ1SQRmVJjgUAoKlg9C7A==$0" rename to "IND\_T\_OBJECT\_NAME" ;  
alter index  "BIN$FfQ1SQRnVJjgUAoKlg9C7A==$0" rename to "PK\_TEST\_OBJECT\_ID" ;  
  
  
**最后执行：**  
flashback table TEST to before drop;  
alter index  "BIN$FfQ1SQRiVJjgUAoKlg9C7A==$0" rename to "IND\_T\_OBJECT\_NAME" ;  
alter index  "BIN$FfQ1SQRjVJjgUAoKlg9C7A==$0" rename to "PK\_TEST\_OBJECT\_ID" ;