oracle 11g drop table 后闪回

--初始化数据

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" ;