FLASHBACK VERSION QUERY

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 ON
E 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

One thought on “FLASHBACK VERSION QUERY