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" ;
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。