Oracle queries recently changed table data

Today, the developer asked me how to quickly and conveniently find the recently changed records of a table. This requirement is very simple. Because it is to check recently changed data, you can check the rollback segments, below is my small experiment.

SQL> Create Table Test (ID number, name varchar2 (10), Gender varchar2 (5); the table has been created. SQL> insert into test values (1, 'song chungun ', 'male'); 1 line has been created. SQL> insert into test values (2, 'yemin ', 'male'); 1 row has been created. SQL> insert into test values (3, 'White ice', 'male'); 1 row has been created. SQL> insert into test values (4, 'fang weisen', 'mal'); 1 row has been created. SQL> insert into test values (5, 'Sun Shuyun ', 'male'); 1 row has been created. SQL> insert into test values (6, 'lib', 'male'); 1 row has been created. SQL> commit; submitted completely.

The following SQL statement can be used to investigate 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 not null order by versions_starttime DESC; ID name gende versions_starttime versions_endtime v ---------- ------ ----- --------------------------- Jun-6 Shi Bo male 30-11 months-11 04.02.28 pm I 5 sun Shu male 30-11 months-11 04.02.28 pm I 1 song Wind male 30-11 months-11 04.02.28 pm I 3 white ice male 30-11 months-11 04.02.28 pm I 2 ye minmale 30-11 months-11 04.02.28 pm I 4 Fang weisen male 30-11 months-11 04.02.28 pm I you have selected 6 rows.

Modify several pieces of data and compare them with the following query.

SQL> Update test set gender = 'female 'Where name = 'Sun Shuyun'; 1 row updated. SQL> commit; submitted completely. SQL> Update test set gender = 'female 'where name = 'lib'; 1 row updated. SQL> commit; submitted completely.

Query the modified data again to view the data before the modification.

SQL> select ID, name, gender, versions_starttime, versions_endtime, versions_operation from test versions between timestamp minvalue and maxvalue where versions_starttime is not null order by versions_starttime DESC; ID name gende versions_starttime versions_endtime v ---------- ------ ------------------------------6 Shi Bo female 30-11 month-11 04.15.07 afternoon U 5 sun shujia female 30-11-11 04.14.31 afternoon U 4 Fang weisen male 30-11 months-11 04.02.28 pm I 3 white ice male 30-11 months-11 04.02.28 pm I 2 ye min male 30-11 months-11 04.02.28 pm I 1 Song Chunfeng male 30-11 months-11 04.02.28 pm I 6 Shi Bo male 30-11-11 04.02.28 PM 30-11-11 04.15.07 pm I 5 sun Shu male 30-11-11 04.02.28 PM 30-11-11 04.14.31 pm I have selected 8 rows.

Modify several more data records before querying.

SQL> Delete from test where name = 'lib'; delete 1 row. SQL> Delete from test where name = 'grandchildren '; 1 row deleted. SQL> commit; submitted completely. SQL> select ID, name, gender, versions_starttime, versions_endtime, versions_operation from test versions between timestamp minvalue and maxvalue where versions_starttime is not null order by versions_starttime DESC; ID name gende versions_starttime versions_endtime v ---------- ------ ----- --------------------------- Jun-5 sun shujia male 30-11 months-11 04.26.02 pm d 6 Shi Bo male 30-11-11 04.26.02 pm D 6 Shi Bo female 30-11-11 04.15.07 PM 30-11-11 04.19.08 pm u 5 sun shujia female 30-11 month-11 04.14.31 PM 30-11 month-11 04.19.08 pm u 2 ye min male 30-11 month- 11 04.02.28 pm I 3 white ice male 30-11 months-11 04.02.28 pm I 4 Fang weisen male 30-11 months-11 04.02.28 pm I 5 sun shuishang male 30-11 months-11 04.02.28 PM 30-11 months pm I 1 Song Chunfeng male 30-11-11 04.02.28 pm I 6 Shi Bo male 30-11-11 04.02.28 PM 30-11-11 04.15.07 pm I have selected 10 rows.

The preceding experiments show that versions_starttime is the start time of data modification, and versions_endtime is the effective time of new data after the data is modified, that is, the versions_starttime and versions_endtime periods, this data item has not been modified. If versions_endtime is empty, it indicates that the record has not been modified since versions_starttime. Versions_operation indicates the modification state. I indicates insert, u indicates update, and D indicates Delete. In this case, if you want to roll back the insert data, you only need to perform the delete reverse operation. If you want to roll back the update operation, you can reverse update the data back, for example, in this experiment, we can see that the update operation has two records named Shi Bo and Sun Shuyun, and we can also see that their gender is male before the update operation, therefore, you only need to perform a reverse update. If you want to roll back the delete operation and perform an insert operation, insert the deleted data.

Note: This SQL can only query the information in the rollback segment, beyond the rollback segment range this SQL can do nothing, need to use the logmgr tool to mine the log (see http://www.dbdream.org /? P = 149 ).