time right before the data change. The other possibility is to use tablespace point in time recovery, but that is time consuming and messy. What if, instead, we could just flash back our table to the point in time we are interested in. Well, with Oracle Database 10g we can! Now, with the flashback table command, you can flash back an Oracle Database 10g table based on timestamp or database SCN. All flashback table operations must be at the beginning of any transaction, and flashback table operations are not supported for the SYS user. Here is an example:
SQL> Commit; SQL> Flashback table emp to SCN 220360; SQL> flashback table emp to TIMESTAMP to_timestamp('2003-09-30 09:00:00', 'YYYY-MM-DD HH:MI:SS');
To be able to flash back a table, the table must have row movement enabled via the alter table enable row movement command (row movement is disabled by default). Enabling row movement is something that can be done just before you issue the flashback table command, so you don't need it enabled all the time. The flashback table command allows you to flash back or flash forward; thus, you can undo the effects of a previous flashback table command. You cannot flash back to a time prior to most DDL operations on the table being flashed back, or before the last time that the database was opened with the resetlogs command. Also, when you execute a flashback table statement, the execution of that statement is recorded in the alert log.
Since you can flash forward, it is a good idea to record the current SCN of the database before you flash back a table. The current SCN is available in the CURRENT_SCN column in the V$DATABASE view. Record the SCN before you issue the flashback table command, not after.
Oracle Database 10g offers enhancements to monitoring the rollback of transactions by SMON, and offers historical information about transaction recovery and rollback operations. This allows you to determine how much work remains during recovery operations. This monitoring is supported via the V$FAST_START_TRANSACTIONS view, which provides information about transactions that Oracle Database 10g is