Flashback Version Query

This is a new feature comes in Oracle 10g. With this you can also get back you present table to a previous state. Like you mistakenly deleted some rows and you have committed and now you want to get back your data.However, it depends on the availability of the undo information in the database, so if the undo information has been aged out, this approach will fail.

Here is an example for your better understanding,

SQL> select * from t;
         A
----------
         1
       100
        10

SQL> insert into t values(2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t;
         A
----------
         1
       100
        10
         2

Flashback Table
SQL> flashback table t to timestamp systimestamp-interval '1' minute;

Flashback complete.

SQL> select * from t;
         A
----------
         1
       100
        10

Flashback Versions Query
You can also keep track of change data over time suppose when one row is upadted when one row is deleted and when one row is inserted by following query,

select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;

SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
  from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;  

Output is,

VERSIONS_STARTT VERSIONS_ENDTIME     VERSIONS_XID     V          A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I          5
.16 PM          M

09-APR-08 02.47                      0700100035010000 D          5
.26 PM

                                                               100
                                                                10
                                                                 1

SQL> insert into t values(200);
1 row created.

SQL> commit;
Commit complete.

SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
  from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;  

VERSIONS_STARTT VERSIONS_ENDTIME     VERSIONS_XID     V          A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I          5
.16 PM          M

09-APR-08 02.47                      0700100035010000 D          5
.26 PM

09-APR-08 02.49                      0700140035010000 I        200
.35 PM

                                                                10
                                                                 1

                                                               100

6 rows selected.

You can find out the changes over time by ,

select a, versions_starttime, versions_endtime
from t versions between timestamp
to_date('7/3/20078 13:33:54','mm/dd/yyyy hh24:mi:ss')
and to_date('7/3/2008 13:37:55','mm/dd/yyyy hh24:mi:ss')

Flashback Transaction Query
You can check undo_sql by,
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '....';

Flashback Query
SQL> select * from flash;
A
----------
1
2

SQL> insert into flash values(3);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from flash as of timestamp systimestamp-interval '1' minute;
A
----------
1
2

SQL> select * from flash;
A
----------
1
2
3

In order to know more about it have a look at,
Restore Old Data from Flashback Query
Related Documents:
Restore Old Data from Flashback Query

Oracle Flashback Technology

Oracle Flashback Drop