Recovering data from an update or delete in Oracle

Problem description

For example, the developer on the employee table to make an UPDATE statement, but after the update, only to find that the updated statement is incorrect, you need to undo the update operation just now. such as (update employee E set e.block= ' 0300100011000000248 ' wheree.block= ' 0300100011000000240 '; Updated 10 data)

1, if in a short period of time, you can query the database versions, record the employee table in a short period of time update, delete, insert operations. (How long the time depends on the relevant settings parameters in the database)

<pre name= "code" class= "SQL" >select Versions_xid, Versions_operation, Versions_starttime, Versions_endtime From   employee   versions  between  timestamp  minvalue  and  MaxValue;

2, if smooth, you can find the statement just update, find the corresponding VERSIONS_XID value, and then query Flashback_transaction_query Flash back to the Transaction query table.

If the value of Versions_xid is ' 000a0019000214c3 '

Select  *  from  flashback_transaction_query  where xid = ' 000a0019000214c3 '

If you want to cancel just the update operation, execute the SQL statement in the Undo_sql field in the database in the query.

The time condition of the query, as follows:

Select  * FROM Flashback_transaction_query  where table_name like ' emp% ' where commit_timestamp< to_date  (' 2009-2-16 10:30:00 ', ' yyyy-mm-dd hh24:mi:ss ')

3 , if the 2nd part of the above is not queried, you can first look at the current employee table of the amount of data, so as not to compare with the previous update

Select COUNT (*) from employee

4. A new table can be produced to back up the flashback data before the update (this also depends on the database flashback time, the size of the undo)

CREATE TABLE Employee_copyasselect * from employee as of  timestamp to_timestamp (' 2009-02-16 10:00:00 ', ' yyyy-mm-dd Hh24:mi:ss ')

 

5, look at the employee table before the update in the UPDATE statement whether the condition is the same (such as the original UPDATE statement condition is e.block= ' 0300100011000000240 ', updated 10), now found to be 10 data, You can find the ID of the following 10 data, and then update it back.

Select  * from  employee_copy  e  where e.block= ' 0300100011000000240 ';

6, find out the employee ID

<pre name= "code" class= "SQL" >select count (*) from the employee where report_id in (' 8a8881a71c9e53f5011ca74049d7001b ' , ' 5f3ba0ea14890024e0437f0000010024 ', ' 5a6f193a11a820d8e0430a08012120d8 ', ' 56f9dfc764e0101ae0430a3e3d64101a ', ' 8a8881a71cd6ea28011cdf6c372d00a3 ', ' 8a8881a71df71207011dfc3071730276 ', ' 8a8881a71df71207011dfc38012b0277 ', ' 8a8881a71df71207011dfc3f2e50027b ', ' 8a8881a71df71207011dfc0d5ef9022b ', ' 5da7e2d9fa2af06ae0430a080121f06a ')

7, update back to the original data

Update employee set block= ' 0300100011000000240 ' where report_id in (' 8a8881a71c9e53f5011ca74049d7001b ', ' 5f3ba0ea14890024e0437f0000010024 ', ' 5a6f193a11a820d8e0430a08012120d8 ', ' 56f9dfc764e0101ae0430a3e3d64101a ', ' 8a8881a71cd6ea28011cdf6c372d00a3 ', ' 8a8881a71df71207011dfc3071730276 ', ' 8a8881a71df71207011dfc38012b0277 ', ' 8a8881a71df71207011dfc3f2e50027b ', ' 8a8881a71df71207011dfc0d5ef9022b ', ' 5da7e2d9fa2af06ae0430a080121f06a ')

This would be tantamount to undoing the original error SQL statement.

8. Finally delete the backup table

drop table Employee_copy

Complete!

Recovering data from an update or delete in Oracle