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;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as hr SQL> show user;User is "hr" SQL> show parameter undo NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     9000undo_tablespace                      string      UNDOTBS1 SQL> truncate table t1; Table truncated SQL> select systimestamp from dual; SYSTIMESTAMP--------------------------------------------------------------------------------31-DEC-11 02.58.26.560000 PM +08:00 SQL> insert into t1 values(1,'jash'); 1 row inserted SQL> commit; Commit complete SQL> update t1 set name ='bob' where id =1; 1 row updated SQL> commit; Commit complete SQL> select systimestamp from dual; SYSTIMESTAMP-----------------------------------------31-DEC-11 02.59.04.318000 PM +08:00 SQL> SQL> SELECT versions_startscn, versions_starttime,  2         versions_endscn, versions_endtime,  3         versions_xid, versions_operation,  4         id, name  5    FROM hr.t1  6    VERSIONS BETWEEN TIMESTAMP  7        TO_TIMESTAMP('2011-12-31 14:58:26', 'YYYY-MM-DD HH24:MI:SS')  8    AND TO_TIMESTAMP('2011-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 into t1 values(2,'clark'); 1 row insertedSQL> insert into t1 values(3,'pig'); 1 row insertedSQL> insert into t1 values(4,'duck'); 1 row inserted SQL> commit; Commit complete SQL> SQL> update t1 set name='kate' where id=2; 1 row updatedSQL> update t1 set name='luna' where id=2; 1 row updated SQL> commit; Commit complete SQL> select systimestamp from dual; SYSTIMESTAMP--------------------------------------------------------------------------------31-DEC-11 03.05.31.071000 PM +08:00 SQL> SQL> SELECT versions_startscn, versions_starttime,  2         versions_endscn, versions_endtime,  3         versions_xid, versions_operation,  4         id, name  5    FROM hr.t1  6    VERSIONS BETWEEN TIMESTAMP  7        TO_TIMESTAMP('2011-12-31 14:58:26', 'YYYY-MM-DD HH24:MI:SS')  8    AND TO_TIMESTAMP('2011-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