FLASHBACK VERSION QUERY

Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.

Flashback version query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.

SQL> CREATE TABLE flashback_version_query_test (id NUMBER(10),description  VARCHAR2(50));

Table created.

SQL> INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN    TO_CHAR(SYSTIMESTAM
-----------   -------------------
    4991867    2021-07-20 22:29:52

SQL> UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;

1 row updated.

SQL> commit;

Commit complete.


SQL> UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN  TO_CHAR(SYSTIMESTAM
-----------  -------------------
    4992195  2021-07-20 22:34:30


SQL>  SELECT versions_startscn, versions_starttime,versions_endscn, versions_endtime, 
versions_xid, versions_operation, description from flashback_version_query_test 
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2021-07-20 22:29:52', 'YYYY-MM-DD HH24:MI:SS') 
AND TO_TIMESTAMP('2021-07-20 22:34:30', 'YYYY-MM-DD HH24:MI:SS')WHERE  id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
---------- --------------------- ------------------ -------------- -----------  -----------
 4992172   20-JUL-21 10.33.41  PM  0D0020001B040000   U THREE                                            
4992136    20-JUL-21 10.32.23  PM   0B00050021040000  U TWO
4992172    20-JUL-21 10.33.41  PM                       ONE



SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, 
versions_xid, versions_operation, description FROM  flashback_version_query_test
VERSIONS BETWEEN SCN  4991867 and 4992195 WHERE  id = 1;  

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
 4992172  20-JUL-21 10.33.41 PM                        0D0020001B040000 U THREE
 4992136 20-JUL-21 10.32.23 PM     4992172 20-JUL-21 10.33.41 PM    0B00050021040000 U TWO
                                                       4992136 20-JUL-21 10.32.23 PM                       ONE



SQL>  SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM   
flashback_transaction_query WHERE  xid = HEXTORAW('06000000FA030000');

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8