Oracle9i New Feature Series: Flashback Query

Flashback query is a new feature in Oracle9i to recover from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database. Using Flashback Query, you can execute queries against a snapshot of data consistent to a point in time in the past either by specifying a system clock time or a System Change Number (SCN).

Since flashback query is done using the “AS OF” clause of the SELECT statement, the developers/users do not need any administrative privileges or DBA intervention. While you query the old data, the table with current data is available for other users.

The flashback clause of the SELECT statement has the following syntax:

Let’s demonstrate the flashback-query using example. Notice that the COMMIT is what completes a transaction.

11:16:47 SQL> select object_name, object_type

11:16:57   2  from test

11:17:01   3  where owner = 'OUTLN';

 

OBJECT_NAME               OBJECT_TYPE

------------------------- ------------------

OL$                       TABLE

OL$HINTS                  TABLE

OL$HNT_NUM                INDEX

OL$NAME                   INDEX

OL$NODES                  TABLE

OL$SIGNATURE              INDEX

ORA$GRANT_SYS_SELECT      PROCEDURE

 

7 rows selected.

 

11:17:08 SQL>

11:17:14 SQL> update test

11:17:22   2  set object_type = 'INVALID'

11:17:53   3  where object_name = 'ORA$GRANT_SYS_SELECT';

 

1 row updated.

 

12:32:12 SQL> delete from test

12:32:18   2  where object_name = 'OL$NODES';

 

1 row deleted.

 

12:32:51 SQL> update test

12:32:58   2  set object_type = 'VALID'

12:33:07   3  where object_name = 'ORA$GRANT_SYS_SELECT';

 

1 row updated.

 

12:33:20 SQL>commit;

 

Commit complete.

 

12:33:23 SQL>

12:37:56 SQL>

12:37:57 SQL> select object_name, object_type

12:38:03   2  from test

12:38:03   3  where owner = 'OUTLN';

 

OBJECT_NAME               OBJECT_TYPE

------------------------- ------------------

OL$                       TABLE

OL$HINTS                  TABLE

OL$HNT_NUM                INDEX

OL$NAME                   INDEX

OL$SIGNATURE              INDEX

ORA$GRANT_SYS_SELECT      VALID

 

6 rows selected.

 

12:38:04 SQL>

12:40:53 SQL> select object_name, object_type

12:41:02   2  from test AS OF TIMESTAMP                               TO_TIMESTAMP('20040226123251','YYYYMMDDHHMISS')

12:42:39   3 where owner = 'OUTLN';

 

OBJECT_NAME               OBJECT_TYPE

------------------------- ------------------

OL$                       TABLE

OL$HINTS                  TABLE

OL$HNT_NUM                INDEX

OL$NAME                   INDEX

OL$NODES                  TABLE

OL$SIGNATURE              INDEX

ORA$GRANT_SYS_SELECT      PROCEDURE

 

7 rows selected.

 

12:42:44 SQL>

12:43:15 SQL> select object_name, object_type

  2 from test AS OF TIMESTAMP TO_TIMESTAMP('20040226123319','YYYYMMDDHHMISS')

  3 where owner = 'OUTLN'

12:43:51 SQL>/

 

OBJECT_NAME               OBJECT_TYPE

------------------------- ------------------

OL$                       TABLE

OL$HINTS                  TABLE

OL$HNT_NUM                INDEX

OL$NAME                   INDEX

OL$NODES                  TABLE

OL$SIGNATURE              INDEX

ORA$GRANT_SYS_SELECT      PROCEDURE

 

7 rows selected.

 

12:43:51 SQL>

By writing proper sub queries and update statements you can recover the rows.