Practices of the Flashback Drop flash deletion function of Oracle

The Flashback Drop flash-back deletion function of Oracle provides a convenient Recovery Method for deleting tables by mistake. The principle of this function is the RecycleBin function of Oracle. Note: If the table to be deleted is originally stored in the SYSTEM tablespace, this function is not supported.

1. Flashback Drop Function
Restore the table that has been mistakenly dropped. After a table is deleted, you can still view the content of the drop table, which is achieved by viewing the content in the recycle bin.

2. Implementation Principle
The deleted table will be stored in the recyclebin recycle bin. After dropping the table, the name of the table will be changed.

3. View related to the recycle bin
DBA_RECYCLEBIN
USER_RECYCLEBIN
RECYCLEBIN

4. display the brief information of the table that the current user has been dropped
SQL> show recyclebin

5. Clear the recycle bin content Conditions
1) Insufficient tablespace
2) insufficient user space quota
3) purge command
4) after you use the flashback command to restore the table, the record content in the corresponding recycle bin is cleared.

6. Flashback Drop syntax
SQL> FLASHBACK TABLE ft_1 TO BEFORE DROP;
SQL> FLASHBACK TABLE "BIN $ Z6gzDCWg7hfgQAB/AQAROQ = $0" TO BEFORE DROP;
Both methods can be used to retrieve the deleted table. The first method is to restore to the last deleted state. The second method is to flash back an object in the recycle bin for restoration scenarios after a table is deleted multiple times.

7. Flashback Drop flash deletion function practices
(1). Create the test table ft_1
Sys @ ora11g> conn secooler/secooler
Connected.
Secooler @ ora11g> create table ft_1 as select * from all_objects;

Table created.

Secooler @ ora11g> select table_name from user_tables where table_name = 'ft _ 1 ';

TABLE_NAME
------------------------------
FT_1

(2) simulate drop ft_1 table
Secooler @ ora11g> drop table ft_1;

Table dropped.

(3) view the recycle bin. The ft_1 table is already in the recycle bin.
Secooler @ ora11g> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------
FT_1 BIN $ vXtL504BE5vgQMKQt8Bnug ==0 0 TABLE 2012-04-12: 20: 14: 24

(4). Demonstrate the query function
Secooler @ ora11g> select count (*) from "BIN $ vXtL504BE5vgQMKQt8Bnug ===$ 0 ";

COUNT (*)
----------
71256

(5). Flash back the drop table
Secooler @ ora11g> flashback table ft_1 to before drop;

Flashback complete.

Secooler @ ora11g> select table_name from user_tables where table_name = 'ft _ 1 ';

TABLE_NAME
------------------------------
FT_1

You can use the following command to restore a table in the recycle bin.
Secooler @ ora11g> drop table ft_1;

Table dropped.

Secooler @ ora11g> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------
FT_1 BIN $ vXtL504IE5vgQMKQt8Bnug ==0 0 TABLE 2012-04-12: 20: 18: 35

Secooler @ ora11g> flashback table "BIN $ vXtL504IE5vgQMKQt8Bnug ===$ 0" to before drop;

Flashback complete.

Secooler @ ora11g> select table_name from user_tables where table_name = 'ft _ 1 ';

TABLE_NAME
------------------------------
FT_1

OK. The deleted table will be restored smoothly.

8. How to clear the content of the recycle bin
If you are sure, and certainly do not want to restore these tables, you can use the following methods to clean up the recycle bin.
1) Clear the recycle bin of the current user
SQL> purge recyclebin;
SQL> purge user_recyclebin;

2) Clear the recycle bin of the specified tablespace tbs_sec_d
SQL> purge tablespace tbs_secooler_d;

3) Clear the specified tablespace tbs_sec_d and specify the user's sec recycle bin.
SQL> purge tablespace tbs_sec_d user sec;

4) Clear all content in the recycle bin (sys user)
SQL> purge dba_recyclebin

9. drop table method without generating recycle bin data
This method is used to completely delete a table. before using this method, consider it clearly.
SQL> drop table ft_1 purge;

10. Summary
Before using the Flashback Drop flash deletion function, you must fully understand the implementation principle of this function, the conditions for using this function, and its restrictions. The flash-back deletion function provides a convenient recovery method after tables are accidentally dropped.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12