oracle11gdroptable後閃回

oracle11gdroptable後閃回

--初始化數據

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