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

Home » Articles » 10g » Here

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.

Related articles.

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.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.