Oracle Database error operation deleted the table, how to recover

One: Recovery of tables

For 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. The general steps are:

1. Query the deleted table from Flash back

SELECT * FROM RecycleBin

2. Performing a table recovery

Flashback table TB to before drop, where the TB represents the name of the table you want to restore.

Second: Table Data recovery

As long as there is no TRUNCATE statement, we can select recovery according to the commit time of the transaction, and the general steps are:

1, the first query from the Flashback_transaction_query view, the view provides the table name for the query, transaction commit time, Undo_sql and other fields.

Example: SELECT * from Flashback_transaction_query where
Table_name= ' TEST '; （this in my actual operation found that there is no table name in the background, but a different table name, so if you can't find the table name, you can try to find the operation time, when the recovery time to try to see which time to recover）

2, the implementation of the table record recovery

The query is usually based on time, the query statement mode is SELECT * from TB as of timestamp
To_timestamp (Time, ' yyyy-mm-dd hh24:mi:ss '); TB refers to the name of a table, which is a point in time

such as SELECT * from Scott.test as of timestamp to_timestamp (' 2009-12-11
20:53:57 ', ' yyyy-mm-dd hh24:mi:ss ');

If you have data, recovery is extremely straightforward, with the statement 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;

The purpose of this command is to allow Oracle to modify the ROWID assigned to the row. In Oracle
, an ROWID is assigned to a row when it is inserted, and the line always has this rowid. The Flashback table handles the EMP
Complete the delete and reinsert the rows so that the rows are assigned a new ROWID. To support flashback, Oracle must be allowed to perform this operation

Oracle Database error operation deleted the table, how to recover