Oracle table and table data recovery

1. Table recovery

For mistakenly deleted tables, you can basically recover from the flashback table area as long as you don't use the purge permanent delete option.

The data table and the data in it can be recovered. Remember that flashback table is provided from Oralce 10g. The general steps are as follows:

a. Query deleted tables from flashback table

Select * from recyclebin order by droptime desc

b. Perform the recovery of the table

Flashback table 'Table name to be restored' to before drop

Back to the top.

two。 Table data recovery

For mistakenly deleted table records, as long as there is no truncate statement, you can choose to restore according to the commit time of the transaction.

This function is also provided by oracle above 10g. The general steps are as follows:

a. Start by querying from the flashback_transaction_query view, which provides fields such as table name, transaction commit time, undo_sql, and so on.

Select * the table name of the data to be recovered by from flashback_transaction_query where table_name=' (uppercase)'

b. Query the time point of deletion

Select to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') time, to_char (dbms_flashback.get_system_change_number) scn from dual

Or you know, probably remember to delete the point, you can also try this query to find out the point in time before the deletion

Select * from 'the name of the table that needs to recover data' as of timestamp to_timestamp ('point in time', 'yyyy-mm-dd hh34:mi:ss')

c. Perform data recovery

Through the second step to find the point in time when the data was lost, the recovery is extremely simple, and the statement is

Flashback table 'table name that needs to recover data' to timestamp to_timestamp ('point in time before data loss', 'yyyy-mm-dd hh34:mi:ss')

Note: when performing the above operation, you need to allow oracle to modify the rowid assigned to the row, and then oracle needs to assign a new physical address to the recovered data.

Alter table table_name enable row movement

In fact, after finding the point in time before the data is lost, the recovery data can also be inserted directly into the target table.

Insert into 'data lost table' select * from t of timestamp to_timestamp ('time', 'yyyy-mm-dd hh34:mi:ss') where.