Query the table data that was modified at a point in the rollback segment in the Oracle database and restore the original data in the table

Use the following SQL to investigate the most recently changed data.

Sql> SELECT id,name,versions_starttime,versions_endtime,versions_operation from TEST VERSIONS between TIMESTAMP MINVALUE and MAXVALUE WHERE versions_starttime is isn't NULL ORDER by Versions_starttime DESC;

Through the small experiment above, we can see that Versions_starttime is the starting time of data modification, Versions_endtime is the effective time of new data after the data has been modified, that is versions_starttime and versions_ Endtime time period, this data has not been modified again, if Versions_endtime is empty, it means that the day record from Versions_starttime time has not been modified. Versions_operation is the modified state, I represents the Insert,u on behalf of Update,d Delete. At this point, if you want to roll back the insert data, only need to delete the reverse operation, if you want to roll back the update operation, reverse the data update back, for example, this experiment can see that the update operation is named Sporting and Sun Shuxiang two records, And also can see the data before the update their sex is male, so only need to do a reverse update, the gender of the male can be implemented back, if you want to roll back the delete operation, also do an insert operation, the deleted data will be inserted back.
Note: This SQL can only query the information within the rollback segment, beyond the scope of the rollback segment this SQL is powerless to dig logs with the help of the Logmgr tool. (See: http://www.cnblogs.com/wzmenjoy/archive/2012/01/17/2367636.html)

Here is a store that I wrote to restore an update of data that was made at a point in a table pub_goods:

Create or Replace procedure Supplytaxrate_to_his_withdate (hisdate in date) is

Cursor C_modifiedgoods is
SELECT distinct goodsid from pub_goods VERSIONS between TIMESTAMP MINVALUE and
MAXVALUE WHERE Versions_starttime is not NULL and Versions_starttime > hisdate;

R_modifiedgoods C_modifiedgoods%rowtype;

Begin


Open c_modifiedgoods;
Loop

Fetch c_modifiedgoods into r_modifiedgoods;
If C_modifiedgoods%notfound Then
Exit
End If;

UPDATE pub_goods AA
SET (Supplytaxratebak) = (select A.supplytaxrate
From Pub_goods VERSIONS between TIMESTAMP MINVALUE and MAXVALUE A,
(SELECT Goodsid,
Min (versions_starttime) versions_starttime
From Pub_goods VERSIONS between TIMESTAMP MINVALUE and MAXVALUE
WHERE Versions_starttime is not NULL
> Versions_starttime
To_date (' 2017-07-01 ',
' Yyyy-mm-dd ')
Group BY Goodsid) b
where A.goodsid = B.goodsid
and a.versions_starttime = B.versions_starttime
and A.goodsid=r_modifiedgoods.goodsid)
where Aa.goodsid=r_modifiedgoods.goodsid;
End Loop;
Commit
Close C_modifiedgoods;

End Supplytaxrate_to_his_withdate;
/

In the above store, I restored the value of the modified field to one of my new Bak fields, depending on how the restore is visible, and the insert and delete operations can also be queried for data and restored by SQL.

Query the table data that was modified at a point in the rollback segment in the Oracle database and restore the original data in the table