Shulou(Shulou.com)06/02 Report--

Oracle's Flashback Drop flashback delete function gives us a convenient recovery way to erroneously DROP delete tables, which is realized by Oracle's "RecycleBin" function. Note that this feature is not supported if the deleted table was originally stored on the SYSTEM system tablespace.

1.Flashback Drop function

Restore the table dropped by the error drop. When a table is deleted, you can still view the contents of the drop table by looking at the contents of the Recycle Bin.

two。 Realization principle

The deleted table will be stored in a place called the recyclebin Recycle Bin. When the drop drops the table, it will actually change the name of the changed table.

3. Views related to the Recycle Bin

DBA_RECYCLEBIN

USER_RECYCLEBIN

RECYCLEBIN

4. Displays the short table information of the current user that has been dropped by drop

SQL > show recyclebin

5. Clear the conditions of the Recycle Bin content

1) insufficient tablespace

2) insufficient space quota for users

3) purge command

4) after using the flashback command to restore the table, 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 of the above methods can achieve the function of retrieving the deleted table. The first method is to restore to the last deleted state; the second method can flashback a specific object in the Recycle Bin for recovery scenarios after a table has been deleted multiple times.

Practice of 7.Flashback Drop flashback deletion function

(1)。 Create a 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 to drop ft_1 table

Secooler@ora11g > drop table ft_1

Table dropped.

(3)。 Check the Recycle Bin and see here that 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 TABLE 2012-04-12 purge 20 purl 14 purl 24

(4)。 Demonstrate the query function

Secooler@ora11g > select count (*) from "BIN$vXtL504BE5vgQMKQt8Bnug==$0"

COUNT (*)

-

71256

(5)。 Flashback the watch dropped by drop

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

Here, when you can confirm which table in the Recycle Bin is to be recovered, you can also use the following command to restore.

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 TABLE 2012-04-12 purge 20 purl 18 purl 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, at this point, the deleted table is successfully restored.

8. The method of clearing the contents of the Recycle Bin

If you are sure, certain, and sure that you do not want to restore these tables, you can use the following methods to clean up the Recycle Bin.

1) clear the current user's Recycle Bin

SQL > purge recyclebin

SQL > purge user_recyclebin

2) clear the Recycle Bin for the specified tablespace tbs_sec_d

SQL > purge tablespace tbs_secooler_d

3) clear the specified tablespace tbs_sec_d and specify the recycle bin for the user sec

SQL > purge tablespace tbs_sec_d user sec

4) clear all contents in the Recycle Bin (sys users)

SQL > purge dba_recyclebin

9. Simultaneous drop Table method without generating Recycle Bin data

This method is a way to delete the table completely, and you should think carefully before using it.

SQL > drop table ft_1 purge

10. Summary

Before using the Flashback Drop flashback delete function, you should fully understand the implementation principle of this function, as well as the conditions for using this feature and its limitations. The flashback delete function provides us with a convenient way to recover after the table is mistakenly DROP.

Good luck.

Secooler

12.04.12

-- The End--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.