Flashback Query

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:

AS OF {SCN | TIMESTAMP} expr

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†† 2from test
11:17:01†† 3where 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†† 2set object_type = 'INVALID'
11:17:53†† 3where object_name = 'ORA$GRANT_SYS_SELECT';
 
1 row updated.
 
12:32:12 SQL> delete from test
12:32:18†† 2where object_name = 'OL$NODES';
 
1 row deleted.
 
12:32:51 SQL> update test
12:32:58†† 2set object_type = 'VALID'
12:33:07†† 3where 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.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.