Oracle 9i introduced the Flashback Query. This feature lets a session query the database as if it were a previous point in time, as if no changes to data had occurred since that time. The Flashback Query is performed with the DBMS_FLASHBACK supplied package. One benefit of Flashback Queries is to recover from accidental changes to data without having to recover the database. Someone just queries the database at a point in time before the changes were made, stores the results, and then uses them to restore the data. This lets your database maintain higher availability. You do not have to bring down the database and perform incomplete recovery to restore the data. Additionally, you will not lose the changes that would have happened past the incomplete recovery point.

Flashback Queries can be thought of a partial recovery. Due to the errant transaction, instead of doing a recovery of an entire tablespace or a database, the data can be directly viewed using a Query.

Flashback Queries can be used in either of two ways:

  • Time Based Flashback
  • SCN Based Flashback

The following depicts an example of a typical errant transaction. A user wanted to delete data from a PRODUCT table based on PROD_GRP=10 condition. Accidentally, the user executes a wrong query, probably without a WHERE clause, and COMMITS the data. Finally, when he queries the table to check the record count, he notices that all the rows from the table are deleted. The Administrator uses the Flashback Query feature to restore the data to prior point of time, the data as existed some time minutes back.

SQL> select prod_grp, count(*) from product group by prod_grp;

---------- ----------
        10         17
        20         14

2 rows selected.

SQL> delete from product;

31 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from product;


1 row selected.

SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

16-07-2005 15:33:56

1 row selected.

SQL> insert into product
  2  select * from product
  3  as of timestamp
  4  to_timestamp('16-07-2005 15:25:00','dd-mm-yyyy hh24:mi:ss');

31 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from product;


1 row selected.

In the above example, the to_timestamp('16-07-2005 15:25:00','dd-mm-yyyy hh24:mi:ss') clause is used to query the table as it existed at the time specified in TIMESTAMP clause. Same data could also be restored using SCN based flashback. In fact, when a timebased flashback is performed, Oracle is really doing SCN based flashbacks. Oracle actually finds an SCN near to the time specified in the AS OF TIMESTAMP clause.

The only change, when using SCN based flashback in the above example would be AS OF SCN (value for scn) clause.

Oracle 10g introduced the Flashback Versions Query. This feature lets you query the history of a given row of data. This is accomplished with the VERSIONS clause of a SELECT statement. For instance, to see the changes to a row of data, your query might look similar to the following:

select versions_xid,salary from emp
version between scn 11567 and 12000
where empno=1001;

The VERSIONS_XID column is a pseudo column indicating the identifier of the transaction that made the change. The valid versions pseudo columns are shown below:

Psuedo Column Description
VERSIONS_STARTTIME Timestamp of the first version
VERSIONS_ENDTIME Timestamp of the last version
VERSIONS_STARTSCN SCN of the first version
VERSIONS_ENDSCN SCN of the last version
VERSIONS_XID Transaction ID of the transaction that created this version
VERSIONS_OPERATION Returns 'I' if an INSERT, 'D' if a DELETE, or 'U' if an UPDATE caused the version

Oracle 10g also introduced the Flashback Transaction Query. This feature is implemented with the FLASHBACK_TRANSACTION_QUERY view. This view lets you see changes made by a particular transaction. A sample query against this view might look as follows:

select xid,table_owner,table_name,operation,undo_sql
from flashback_transaction_query
where logon_user='SCOTT';

This query will look at all transactions the user SCOTT has made that are still retained in the undo tablespace. The operation that SCOTT attempted ('I' = Insert, 'U' = Update, 'D' = Delete) and the table owner and name are given. The SQL statement to undo this transaction is also given.

Oracle 10g also introduced the Flashback Table. This feature lets you recover a table to a previous point in time. This feature is implemented with the flashback table SQL statement. In order to use this SQL statement, you must have the flashback any table system privilege or the flashback object privilege. You must also have select, insert, update, and delete object privileges. The information required to perform the flashback operation must still be available in the undo tablespace. Finally, row movement must be enabled for the table to be flashed back. This is done with the alter table command similar to the following:

alter table emp enable row movement;

If all the above requirements are met, a table can be flashed back similar to the following:

flashback table emp to timestamp
   to_timestamp('2004-09-01 12:13:06','YYYY-MM-DD HH24:MI:SS');

The above example reverts the EMP table back to the given point in time. Multiple tables can be flashed back similar to the following:

flashback table scott.emp,hr.depts to timestamp
   to_scn 178648;

The above example will flashback two tables to a specific SCN.

While executing the flashback table command, DML locks are placed on the table. This prevents other DML commands (insert, update, or delete)from taking place on the table until the flashback operation is complete. The flashback table command is executed as a single transaction regardless of how many tables are participating in the flashback operation.

It is important to note that you cannot rollback the execution of a flashback table command. However, you can issue another flashback table command to the point in time or SCN right before your original flashback table command.

There are some restrictions to flashing back a table. You cannot flash back the following:

  • Tables that are part of a cluster
  • Materialized Views
  • Advanced Queuing tables
  • Data Dictionary tables
  • System tables
  • Remote tables
  • Object tables
  • Nested tables
  • Individual partitions or subpartitions without flashing back the entire table.

Oracle 10g also introduced the ability to recover a dropped table. This is done with the Recycle Bin. Think of the Recycle Bin as you would of the Recycle Bin in Windows. In Oracle 10g, when a table is dropped, it is not removed from the system and the space is not reclaimed. Instead, the table is moved to the Recycle Bin. The table is given a system-generated name so that objects in the Recycle Bin are unique. The tables in the Recycle Bin can be seen by querying the DBA_RECYCLEBIN view.

SELECT owner,object_name,original_name,drop_time FROM dba_recyclebin;

To recover a dropped table, use the flashback table command with the before drop clause. For instance, to recover an accidentally dropped table, issue a command similar to the following:

flashback table scott.emp to before drop;

Just like the Windows Recycle Bin, allocated space is not reclaimed until the table is removed from the Recycle Bin. The new purge command can be used to remove a table, and index, or the complete contents of the Recycle Bin. To purge one table from the recycle bin, use a command similar to the following:

purge table scott.emp;

To purge all tables from the current user's recycle bin, use the following command:

purge recyclebin;

If you have signed on with SYSDBA privileges, you can remove all objects from all recycle bins with the following command:

purge dba_recyclebin;

Finally, just those objects from a specific tablespace can be purged from the recycle bin with a command similar to the following:

purge tablespace user_data;

Oracle 10g also introduced the Flashback Database feature. This feature lets you restore the entire database to a point in time or prior SCN. This feature is performed with the flashback database SQL command.

flashback database to scn 178648;

Now that the database has reverted to a prior SCN, it is not writeable until you open the database with the resetlogs option as follows:

alter database open resetlogs;

You cannot flash back the database to a point prior to a previous resetlogs operation. You must be signed on with SYSDBA privileges to be able to perform this operation. In order to flash back the database, you must perform these steps:

  1. connect / as sysdba
  2. shutdown immediate
  3. startup mount
  4. alter database flashback on;
  5. flashback database....;
  6. alter database open resetlogs;

The database must be in MOUNT mode and have flashback turned on. At that point, the database can be flashed back to a point in time or to a prior SCN. Finally, the database is opened with resetlogs.

The amount of flashback data for the database is controlled with the db_flashback_retention_target initialization parameter. The default value for this parameter is 1,440 minutes (24 hours). The V$FLASHBACK_DATABASE_LOG view can be used to determine how far you can realistically flash back to. If you need to flash back further than this view will show that you can, your only option is to perform incomplete recovery from your backups. When you start the flashback of the database, Oracle starts a new 10g background process called RVWR (Recovery Writer).

All flashback features require the automated undo. Flashback features are not available if you are using the manual rollback segments. Your flashback operation can only go as far back in time as the undo_retention initialization parameter will allow. If you need to go back further in time, you will need to increase this parameter.