Plsql_ Flash back Delete Flashback Delete Table Delete how to recover (case)

2014-07-02 Baoxinjian

I. Summary

In the Plsql development, sometimes encountered in the table data mistakenly deleted, in fact, encountered this situation does not need to tense, if the problem is large, please DBA help, if only small problem, just deal with their own, using flashback flash back operation, Cocoa will restore the table

When the data is deleted, an SCN and timestamp are automatically recorded, and the data deleted by the SCN and timestamp can be queried by the system.

1.SCN concept

The SCN is a number that is automatically maintained by the DBMS to accumulate increments after the Oracle database is updated. There are 4 types of SCN in the Oracle database, respectively

System checkpoint SCN: The system checkpoint SCN is in the control file, and when the checkpoint process starts (CKPT), Oracle stores the SCN of the system checkpoint in the control file

2. Some prerequisites when using the flash back operation

• Flashback permissions for this table
• Select\insert\delete\alter permission to own the table
• Must be guaranteed to represent row movement
• Cannot recover using flashback when deleting data in table with purge parameter

3. Flash back Query

• function: Used to query the previous deleted data, in order to perform a delete recovery, there is a correct SCN or timestamp, the system did not recover
• Syntax: SELECT * from Bxj_tst_recyclebin as of [SCN or timestamp]

4. Flash Back Recovery

• Role: Real recovery of data deleted by the table through confirmed SCN and timestamp
• Syntax: Flashback table Bxj_tst_recyclebin to [SCN or timestamp]

5. Before the flashback query is resumed, execute the following statement

ALTER TABLE bxj_tst_recyclebin enable row movement

Ii. Case 1-flashback recovery via timestamp

Step1. Create 8 test data as follows

Step2. Delete 4 of these data, after commit cannot rollback, recover through flashback

Step3.1 determines the time stamp to be recovered by the time interval and, before recovery, the event is stamped with 8 data through a flashback query to determine the time stamp for recovery

ALTER TABLE bxj_tst_recyclebin enable row movement

The Step3.2 is restored directly through a specific timestamp, and, before recovery, by a flashback query, the event is stamped with 8 data to determine the time stamp for recovery

Step4. Recovery via Flashback

Flashback table Bxj_tst_recyclebin to timestamp (systimestamp-interval ' second ');
Flashback table Bxj_tst_recyclebin to timestamp (To_timestamp (to_date (' 20140702 11:40:00 ', ' YYYYMMDD HH:MI:SS '));

Step5. After the recovery is complete, there is no need to flash back the query, that is, you can query to the 4 deleted data

Iii. Case 2-flashback recovery via SCN

Step1. Once the system commits a thing, the system generates an SCN, and once the data is found to be deleted, the current SCN is queried immediately.

Step2. Through the flashback query, confirm whether the SCN can find out the 4 deleted data

Step4. After the SCN is confirmed, the flashback recovery operation

Flashback table Bxj_tst_recyclebin to SCN 10132214657206

Step5. After the recovery is complete, there is no need to flash back the query, that is, you can query to the 4 deleted data

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