8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Flashback Version Query (VERSIONS BETWEEN) in Oracle Database 10g

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

Create a test table with a single row and check the current SCN and time.

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

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

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

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     725202 2004-03-29 14:59:08

SQL>

Perform a couple of updates, with a commit between each, and check the SCN and time again.

UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
COMMIT;
UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
COMMIT;

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

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     725219 2004-03-29 14:59:36

SQL>

Display the versions of the data between the times captured previously using the VERSIONS BETWEEN clause.

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200

SELECT versions_startscn, versions_starttime, 
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description  
FROM   flashback_version_query_test 
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-03-29 14:59:08', 'YYYY-MM-DD HH24:MI:SS')
                        AND TO_TIMESTAMP('2004-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
WHERE  id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
            725212 29-MAR-04 02.59.16 PM                                                02001C0043030000 U THREE
            725209 29-MAR-04 02.59.16 PM                725212 29-MAR-04 02.59.16 PM    0600030021000000 U TWO
                                                        725209 29-MAR-04 02.59.16 PM                       ONE

SQL>

Repeat the query, but this time using the SCNs captured previously.

SELECT versions_startscn, versions_starttime, 
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description  
FROM   flashback_version_query_test 
       VERSIONS BETWEEN SCN 725202 AND 725219
WHERE  id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
            725212 29-MAR-04 02.59.16 PM                                                02001C0043030000 U THREE
            725209 29-MAR-04 02.59.16 PM                725212 29-MAR-04 02.59.16 PM    0600030021000000 U TWO
                                                        725209 29-MAR-04 02.59.16 PM                       ONE

SQL>

In both cases we see the versions of the DESCRIPTION column during the period, along with the SCN and times of the changes, as well as the transaction ID of the change.

The available pseudocolumn meanings are as follows.

VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN or TIMESTAMP.
VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN or TIMESTAMP.
VERSIONS_XID - ID of the transaction that created the row in it's current state.
VERSIONS_OPERATION - Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)

The boundaries of the version query can also be defined using the MINVALUE and MAXVALUE keywords.

Considerations

There are a few things to consider when using flashback query.

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.
The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.
Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.