Oracle Flash-back use and delete stored procedure recovery

There are two methods: SCN and time stamp two methods of recovery.

I. Recovering deleted and committed data through the SCN

1. Obtain the SCN number of the current database

Select Current_scn from V$database; (Switch to SYS user or System user query)

The SCN number to be queried is: 1499223

2. Check the SCN before the current SCN number

SELECT * from table name as of SCN 1499220; (Determine if the deleted data exists, and if so, recover the data; if not, continue to reduce the SCN number)

3. Recover deleted and submitted data

Flashback table name to SCN 1499220;

Ii. recovery of deleted and submitted data by time

1. Query the current system time

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

2. Query the data at the point in time to delete data

SELECT * from table name as of timestamp To_timestamp (' 2013-05-29 15:29:00 ', ' yyyy-mm-dd hh24:mi:ss '); (if not, continue narrowing)

3. Recover deleted and submitted data

Flashback table name to timestamp to_timestamp (' 2013-05-29 15:29:00 ', ' yyyy-mm-dd hh24:mi:ss ');

Note: If you execute the above statement, an error occurs. You can attempt to execute ALTER TABLE name to enable row movement; Allow change of time stamp

One: Recovery of the table       on the mistakenly deleted table, as long as not using the Purge permanent delete option, then recover from the flash back area hope is quite large. General steps are: 1, from the Flash back query the deleted table     SELECT * from recyclebin2. Perform table recovery    flashback table  TB  to b Efore drop, where the TB represents the name of the table you want to restore. Second: Table data recovery     on the record of accidental deletion, as long as there is no TRUNCATE statement, you can choose to recover according to the time of the transaction submission, the general steps are:     1, first from flashback_transaction _query View, the view provides a table name for the query, transaction commit time, Undo_sql and other fields.       For example: SELECT * from Flashback_transaction_query where table_name= ' TEST ';     2, perform table record recovery & nbsp     Generally first query according to time, the query statement mode is SELECT * from TB as of timestamp to_timestamp (timing, ' yyyy-mm-dd hh24:mi:ss '); TB refers to the name of the table, time refers to a point in the dot       such as SELECT * from Scott.test as of timestamp to_timestamp (' 2009-12-11 20:53:57 ', ' yyyy- Mm-dd hh24:mi:ss ');    If there is data, recovery is extremely simple, the statement is Flashback table TB to timestamp to_timestamp (time, ' Yyyy-mm-dd Hh24:mi: SS ');    such as flashback table Scott.test to timestamp to_timestamp (' 2009-12-11 20:47:30 ', ' yyyy-mm-dd hh24:mi:ss ') ;  NOTE: ALTER TABLE testvarchar enable row movement;

Oracle Flash back use and delete stored procedure recovery