oracle 11g drop table 后闪回
浏览数：27 /
时间：2015年06月12日
--初始化数据
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" ;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
