Flashback table quick recovery of accidentally deleted data

In Oracle Database Operations, accidental data deletion and table drop often occur. In the past, after this happens, developers often seek help from DBAs or administrators to use database backup and recovery to retrieve the data.

From oracle 9i, 10 Gb, you do not have to use backup to restore. There is a fast recovery method called flashback. You can use flashback query or flashback table.

I. FLASHBACK QUERY

Adaptation scope: The data in the table is constantly changing. You need to see that some records have been deleted and modified by mistake at a certain time point. You can recover the data based on these records.

-- Flashing back to 15 minutes ago

Select * from orders as of timestamp (effecimestamp-interval '15' minute) where ......

Here, you can replace minute with DAY, SECOND, and MONTH, for example:

SELECT * FROM orders as of timestamp (SYSTIMESTAMP-INTERVAL '2' DAY)

-- Returns to a certain time point.

Select * from orders as of timestamp to_timestamp ('01-Sep-04 16:18:57. 100', 'dd-Mon-RR HH24: MI: SS. ff') where...

-- Two days ago

Select * from orders as of timestamp (sysdate-2) where .........

Ii. FLASHBACK TABLE

1. You can use the drop table to restore the table quickly.

Flashback table orders to before drop;

If the drop table has already created a new table with the same name, add the rename to clause.

Flashback table order to before drop rename to order_old_version;

2. After the table data is deleted or modified incorrectly, no subsequent data changes can be made and can be quickly restored.

Step 1: First enable row migration

Alter table order enable row movement;

Step 2: flashback table

To 15 minutes ago:

Flashback table order to timestamp incluimestamp-interval '15' minute;

To a certain time point:

Flashback table order to timestamp TO_TIMESTAMP ('2017-09-12 01:15:25 PM ', 'yyyy-MM-DD HH: MI: SS am ')