Oracle query table recently changed data

Sql> CREATE TABLE Test (ID number,name varchar2 (Ten), Gender VARCHAR2 (5)); sql> INSERT INTO test values (1, ' Song Chunli ', ' Male '); 1 rows have been created. sql> INSERT INTO test values (2, ' ye min paid ', ' Male '); 1 rows have been created. sql> INSERT INTO test values (3, ' Bai Bing ', ' Male '); 1 rows have been created. sql> INSERT INTO test values (4, ' Wei Sen ', ' Male '); 1 rows have been created. sql> INSERT INTO test values (5, ' Sun Shuxiang ', ' Male '); 1 rows have been created. sql> INSERT INTO test values (6, ' sporting ', ' Male '); 1 rows have been created. Sql> commit; commit completed.

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 not null order by versions_starttime desc;         ID NAME   GENDE VERSIONS_STARTTIME         VERSIONS_ENDTIME           V---------- ------ ----- ------------------------- ------------------------- -          6  Sporting     male     30-11 Month-11  04.02.28  pm                              I          5&nbSP; Sun Shuxiang   Men     30-11 month -11 04.02.28  pm                               I         1  Song Chunli   Men      30-11 Month -11 04.02.28  pm                              I          3  Bai Bing     male     30-11 Month -11  04.02.28  pm                              I          2  ye min paid     male     30-11 -11 04.02.28  pm                              I         4  Fang   Male     30-11 Month -11 04.02.28  pm                               i has selected 6 rows.

Modify several data to compare with the next query.

sql> update TEST SET gender= ' female ' WHERE name= ' Sun Shuxiang '; 1 rows updated. Sql> commit; commit completed. sql> update TEST SET gender= ' female ' WHERE name= ' sporting '; 1 rows updated. Sql> commit; commit completed.

Once again, the modified data can see the data before it is modified and modified.

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  Sporting         30-11 Month-11  04.15.07  pm                              U          5  Sun Shuxiang       30-11 month -11 04.14.31  pm                               U         4  Fang   Men      30-11 Month -11 04.02.28  pm                              i          3  Bai Bing     men      30月-November -11 04.02.28  pm                              I          2  ye min paid     male     30-11 month -11  04.02.28  pm                              I         1  Song Chunli   Male     30-11 Month -11 04.02.28  pm                               I         6  Sporting     men      30-11 -11 04.02.28  pm   30-11 Moon -11 04.15.07  pm   I          5  Sun Shuxiang   male     30-11 month -11  04.02.28  pm   30-11 -11 04.14.31  pm   i has selected 8 rows.

After modifying a few data and then querying.

Sql> delete from test where name= ' sporting '; deleted  1  line. Sql> delete from test where name= ' Sun Shuxiang '; deleted  1  line. sql> commit; submission completed. 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---------- ------ ----- ------------------------- ------------------------- -          5  Sun Shuxiang   male     30-11 month -11  04.26.02  pm                              D         6  Sporting     Male     30-11 Month -11 04.26.02  pm                               D         6  Sporting     Women      30-11 -11 04.15.07  pm   30-11 Moon -11 04.19.08  pm   U          5  Sun Shuxiang   women     30-11 Month -11 04.14.31   Afternoon   30-11 Month -11 04.19.08  pm   U          2  ye min paid     male     30-11 -11 04.02.28  pm                               I         3  Bai Bing     men      30-11 Month -11 04.02.28  pm                              I          4  Fang   male     30-11 month -11  04.02.28  pm                              I          5  Sun Shuxiang   Male     30-11 -11 04.02.28  pm    30月-November -11 04.14.31  pm   I         1  Song Chunli   Male     30-November -11 04.02.28  pm                              I          6  Sporting     male     30-11 Month -11  04.02.28  pm   30-11 -11 04.15.07  pm   i has selected 10 rows.

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, need to use the Logmgr tool mining logs (see http://www.dbdream.org/?p=149).

Oracle query table recently changed data