Oracle: flashback version query example

Flashback version query is not a new feature of Oracle, but the 10g and 11g functions are enhanced on the basis of 9i, which may be used in some occasions. The following example shows his concept;

ConnectedToOracleDatabase11g Enterprise Edition Release 11.2.0.1.0 ConnectedAsHr SQL> showUser; User Is "Hr" SQL> show parameter undo NAMETYPE VALUE ----------------------------------------------------------------------------- Undo_management string AUTO Undo_retentionInteger9000 Undo_tablespace string UNDOTBS1 SQL>Truncate TableT1; TableTruncated SQL>SelectSystimestampFromDual; SYSTIMESTAMP -------------------------------------------------------------------------------- 31-DEC-11 02.58.26.560000 PM + 08:00 SQL>Insert IntoT1Values(1,'Jash'); 1 row inserted SQL>Commit; CommitComplete SQL>UpdateT1Set Name='Bob' WhereId = 1; 1 row updated SQL>Commit; CommitComplete SQL>SelectSystimestampFromDual; SYSTIMESTAMP ----------------------------------------- 31-DEC-11 02.5900004.318000 PM + SQL> SQL>SELECTVersions_startscn, versions_starttime, 2 versions_endscn, versions_endtime, 3 versions_xid, versions_operation, 4 id,Name 5FROMHr. t1 6 VERSIONSBETWEEN TIMESTAMP 7 TO_TIMESTAMP ('2017-12-31 14:58:26','Yyyy-MM-DD HH24: MI: ss') 8ANDTO_TIMESTAMP ('2017-12-31 14:59:04','Yyyy-MM-DD HH24: MI: ss');

The title of the query result column is a little long. I have truncated it. Otherwise, the whole row cannot be displayed. Check the column title against the preceding query statement;

SQL> SQL>Insert IntoT1Values(2,'Clark'); 1 row inserted SQL>Insert IntoT1Values(3,'Pig'); 1 row inserted SQL>Insert IntoT1Values(4,'Duck'); 1 row inserted SQL>Commit; CommitComplete SQL> SQL>UpdateT1Set Name='Kate' WhereId = 2; 1 row updated SQL>UpdateT1Set Name='Huna' WhereId = 2; 1 row updated SQL>Commit; CommitComplete SQL>SelectSystimestampFromDual; SYSTIMESTAMP -------------------------------------------------------------------------------- 31-DEC-11 03.05.31.071000 PM + 08:00 SQL> SQL>SELECTVersions_startscn, versions_starttime, 2 versions_endscn, versions_endtime, 3 versions_xid, versions_operation, 4 id,Name 5FROMHr. t1 6 VERSIONSBETWEEN TIMESTAMP 7 TO_TIMESTAMP ('2017-12-31 14:58:26','Yyyy-MM-DD HH24: MI: ss') 8ANDTO_TIMESTAMP ('2017-12-31 15:05:31','Yyyy-MM-DD HH24: MI: ss');

Brief description:

Row 6: start_scn: 3852006 end_scn: 3852023 operation: I (insert) indicates inserting values (1, 'jash ')

The fifth line: start_scn: 3852023 operation: U (update) indicates that the record with 'bob' id 1 is updated. Therefore, the newly inserted data in Row 6 is updated;

......

......

You can see that the version record of the row of interest can be found through the query, which must exist in the undo rollback segment and commit committed transactions;

Finally, let's take a look at the overview of the official documentation:

Oracle Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. they rely on undo data, which are records of the effects of individual transactions. for example, if a user runs an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data. undo data is persistent and has ves a database shutdown.

By using flashback features, you can use undo data to query past data or recover from logical damage. Besides using it in flashback features, Oracle Database uses undo data to perform these actions:

• Roll back active transactions

• Recover terminated transactions by using database or process recovery

• Provide read consistency for SQL queries