Plsql_ Flash Back Delete Flashback drop table Delete How to recover (case)

2014-06-25 Baoxinjian

I. Summary

In the Plsql development, sometimes encountered on the table of the accidental deletion, in fact, encountered this situation does not need to be nervous, if the problem is large, please DBA help, if only small problem, only to deal with their own, using flashback flash back operation, you can restore the table

When you delete a table, the system is usually put into the Recycle Bin, and is not completely deleted, so just recover from the Recycle Bin, the principle and the same as the Recycle Bin in window, but once the data in the Recycle Bin is deleted again, this is difficult to recover, only to the DBA for help

1. Concept of Recycle Bin

The Recycle Bin is a virtual container that holds all objects that have been deleted.

Essentially, for the deletion of an object, the database is simply a rename operation.

The database parameter recyclebin is set to ON. (That is, the default setting). The parameter recyclebin is on, which means that the drop object is placed in the Recycle Bin. is off, the object is deleted directly instead of being put into the Recycle Bin.

2. View the RecycleBin value command

Sql> Show parameter bin;

NAME TYPE VALUE

-----------------------------------------------

RecycleBin string on

3. The command to modify the parameter RecycleBin value at the system or session level is

Sql>alter system Set RecycleBin = On|off;

Sql>alter Session Set RecycleBin = On|off;

4. Operations related to the Recycle Bin

View information about the Recycle Bin views are: Recyclebin/user_recyclebin/dba_recyclebin.

The information for manually clearing the Recycle Bin is: Purge RecycleBin (or purge table original_name);

The command to delete a table without putting it into the Recycle Bin is: DROP table table_name purge;

Second, detailed analysis

Case: Restore the table bxj_tst_recyclebin that was mistakenly deleted and restore the index in the table as well

Step1. Creating test tables, test indexes, and test data

1 /*Create a test table*/2 Create TableBxj_tst_recyclebin (3 creation_date Date,4Commentsvarchar2( -)5 );
6 /*To Create a test index*/7 Create IndexInd_creation_date onBxj_tst_recyclebin (creation_date);
8 /*Create test Data*/9 Insert  intoBxj_tst_recyclebinValues(Sysdate,'BXJ TEST recyclebin 1');Ten Insert  intoBxj_tst_recyclebinValues(Sysdate,'BXJ TEST recyclebin 2'); One Insert  intoBxj_tst_recyclebinValues(Sysdate,'BXJ TEST recyclebin 3'); A Insert  intoBxj_tst_recyclebinValues(Sysdate,'BXJ TEST recyclebin 4'); - Insert  intoBxj_tst_recyclebinValues(Sysdate,'BXJ TEST recyclebin 5');

Step2. Before deleting a table, confirm all data in the table, total over 5 records

Step3. Delete a table

drop table Bxj_tst_recyclebin

Step4. Recovering a table

Step4.1 view table is stored in User_recyclebin, object name is changed to Bin$/kuwk5m0ntfgqdqkbgxfbw==$0

The data in the Step4.2 table is still stored in the object in the Recycle Bin (bin$/kuwk5m0ntfgqdqkbgxfbw==$0), in fact, the deletion process, just modify an object name

Step4.3 Recovery of a table by flashback

Flashback table Bxj_tst_recyclebin to before drop

Step4.4 Restore the table, confirm that the data is all right, the data is low when the comparison with select * Details, when the data is more than the total number of select count (*) to be compared

Step5. Recovering an index in a table

After the Step5.1 table is restored, you can find that the table corresponding to the index is also in the Recycle Bin

Step5.2 index recovery by alert index name

Alter index "BIN$/KUWK5MZNTFGQDQKBGXFBW==$0" Rename to Ind_creation_date

Step5.3 confirm that the relationship between the recovered indexes and tables is normal

Abalone New ********************