FlashbackTable flashback to drop table (recycle bin flashback) bitsCN.com
Flashback Table flash back to drop Table (recycle bin flash back)
The recycle bin function of oracle is determined by the recyclebin parameter. the default value is on;
Alter session set recyclebin = on; alter system set recyclebin = on scope = both; alter session set recyclebin = off; alter system set recyclebin = off scope = both; restore the deleted table from the recycle bin to view the deleted table information. SQL> select owner, object_name, original_name, type, ts_name, droptime, can_undrop from dba_recyclebin where owner = 'Scott '; -- The delete object system is named in the format of BIN $ unique_id $ version. the option "can_undrop" is "no" and cannot be recovered, for example, if the index depends on the corresponding table, you must restore the table first. The deleted table has been cleared from the recycle bin, and cannot flash back.
View the show recyclebin object in the recycle BIN; select * from user_recyclebin/dba_recyclebin; manually clear the table purge table "BIN $ 6Ah1cQuityrgQAB/AQBjbQ = $0" in the recycle BIN "; clear the names of all objects in the specified tablespace in the recycle bin, purge tablespace, and purge tablespace tablespace_name user scott for all objects in the specific tablespace in the recycle bin, and purge dba_recyclebin for all objects in the database recycle bin; -- the administrator clears the recycle bin purge recyclebin in all modes; -- normal users clear their Recycle bin
Restore a table using the recycle bin
1.
SQL> flashback table "BIN$5jXWCqj0Ad/gQAB/AQA2zQ==$0" to before drop;SQL> flashback table test01 to before drop;SQL> flashback table test01 to before drop rename to test02;
2.
Query index name, changed to the original name. if it is a constraint, you need to change the constraint name:
SQL> select index_name, table_owner,table_name from user_indexes;SQL> select table_name,constraint_name from USER_CONSTRAINTS;SQL> alter table test01 rename constraint "BIN$5jYrRNeD9gfgQAB/AQA3rg==$0" to test01_pk;SQL> alter index "BIN$5jYrRNeB9gfgQAB/AQA3rg==$0" rename to test01_pk;
-- If a table is deleted multiple times, to restore a table of earlier versions, you must specify the table name in the recycle bin.
-- If you specify the original table name in the command, the latest deleted version will be restored.
-- If the table name has been re-created after deletion, you need to use the rename to option to re-specify other table names.
The deleted table can also be queried.
SQL> select owner,object_name,original_name,type,can_purge,droptime from dba_recyclebin where owner='SCOTT';OWNER OBJECT_NAME ORIGINAL_NAME TYPE CAN DROPTIME------------------------------ ------------------------------ -------------------------------- ------------------------- --- -------------------SCOTT BIN$5jYrRNeG9gfgQAB/AQA3rg==$0 TEST01 TABLE YES 2013-09-13:04:59:31SQL> desc "BIN$5jYrRNeG9gfgQAB/AQA3rg==$0";SQL> select * from "BIN$5jYrRNeG9gfgQAB/AQA3rg==$0"; ID NAME---------- -------------------------------- 1 xycxyc
BitsCN.com