Flashback Version Query

Flashback Version Query

You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.

Lets do some actions..............

C:\>sqlplus flash/flash

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 5 12:10:57 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> create table fvq (id number);

Table created.

#############VERSION 1#############

SQL> insert into fvq values(1);

1 row created.

SQL> commit;

Commit complete.

#############VERSION 2#############

SQL> update fvq set id=2;

1 row updated.

SQL> commit;

Commit complete.

#############VERSION 3#############

SQL> update fvq set id=3;

1 row updated.

SQL> commit;

Commit complete.

#########Here is a typical Flashback Version Query:###############

SQL> SELECT versions_startscn, versions_starttime,

  2         versions_endscn, versions_endtime,

  3         versions_xid, versions_operation,

  4         id

  5    FROM fvq

  6  VERSIONS BETWEEN TIMESTAMP

  7        TO_TIMESTAMP('2011-04-05 12:12:00', 'YYYY-MM-DD HH24:MI:SS')

  8  AND TO_TIMESTAMP('2011-04-05 12:13:00', 'YYYY-MM-DD HH24:MI:SS');

VERSIONS_STARTSCN, VERSIONS_STARTTIME

Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This identifies the time when the data first took on the values reflected in the row version. You can use this to identify the past target time for a Flashback Table or Flashback Query operation.

If this is NULL, then the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_ENDSCN, VERSIONS_ENDTIME

SCN or TIMESTAMP when the row version expired. This identifies the row expiration time.

If this is NULL, then either the row version was still current at the time of the query or the row corresponds to a DELETE operation.

VERSIONS_XID

Identifier of the transaction that created the row version.

VERSIONS_OPERATION

Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation.

Note: For user updates of an index key, a Flashback Version Query may treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I VERSIONS_OPERATION.

#########OUTPUT IS:######

VERSIONS_STARTSCN    VERSIONS_STARTTIME  VERSIONS_ENDSCN        VERSIONS_ENDTIME      VERSIONS_XID                VERSIONS_OPERATION                ID

689271  05.04.11 12:12:41.000                                     010009005E010000           U             3

689262  05.04.11 12:12:29.000     689271  05.04.11 12:12:41.000     06000F0063010000           U             2

                                689262  05.04.11 12:12:29.000                                     1

#########checking current value##############

SQL> select * from fvq;

        ID

----------

         3

#################SINCE NOW U GOT THE SCN OF THE PREVIOUS VERSION VALUE U MAY UPDATE IT AS PER SCN##############

SQL> update fvq set id= (select id from fvq as of scn 689262);

1 row updated.

###############QUERING NOW THE UPDATED VALUE###########

SQL> select * from fvq;

        ID

----------

         2

As u see it is now updated as per your requirement..

:-) ENJOY