oracle 11g drop table 后闪回
分类：SQL语句学习
–初始化数据
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 TABLEBIN$FfQ1SQRnVJjgUAoKlg9C7A==$0 PK_TEST_OBJECT_ID DROP INDEXBIN$FfQ1SQRmVJjgUAoKlg9C7A==$0 IND_T_OBJECT_NAME DROP INDEX–生成闪回表的语句select ‘flashback table ‘ || r.original_name || ‘ to before drop;’ cc from recyclebin rwhere type = ‘TABLE’;CC————————————–flashback table TEST to before drop;–索引会被闪回，但名称会被改select ‘alter index "’ || r.object_name || ‘" rename to "’ || r.original_name || ‘" ;’ ccc from recyclebin rwhere 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" ;
上一篇ORA-02049: 超时: 分布式事务处理等待锁诊断下一篇Oracle expdp为什么比exp快，，原理是什么
顶0踩0
数最亮的星。如果有可能，我带你去远行。
