Flashback Table in Oracle 10g

Description:Flashback Table can be used to recover one or more tables to a specific point in time. It rolles back only the changes made to the table or tables and their dependent objects, such as indexes. Flasback Table undoes recent transactions to an existing table, whereas Flashback Drop recovers a dropped table

Based on: UNDO-Tablespace

Privilege to use:  FLASHBACK ANY TABLE - System privilege and the appropriate object privilege
(Select, Insert, Update, Delete and ALTER)

Usage Syntax:


 

FLASHBACK TABLE table_1 [, table_x]
       TO {SCN | TIMESTAMP [expr] | RESTORE POINT restore_point}
       [{ENABLE | DISABLE} TRIGGERS];

Considerations:

• To be able to flashback a table, you must enable row movement for the table, also ROWIDs will be changed in the Table.
• Integrity constraints are not violated when one or more tables are flashed back.
• It can not be used if the flasback operation crosses a table structure change or a table shrink operation.
• Applying the UNDO_RETENTION initialization parameter to RETENTION GUARANTEE can cause the tablespace running out of space and database hangs.
• UNDO_RETENTION must be greater than the time-Intervall used to recover the table (Recovering a Table can be done if there are undo-records retained in the UNDO Tablespace.
• Flasback Table causes an Exclusive DML-Lock on the tables during recovery.
• By default, the database disables triggers on the affected table before performing a FLASHBACK TABLE operation, and after the operation returns them to the state they were in before the operation (enabled or disabled).
• Turning on the Triggers on the table during FLASHBACK TABLE can be made with the parameter 'ENABLE TRIGGERS'.
• If you either flasback too far or not far enough, you can simply rerun the FLASHBACK TABLE command with a different time stamp or SCN, as long as the undo data is still available.
• All the tables must be flashed back successfully or the entire transaction is rolled back.
• All flashback table operations must be at the beginning of any transaction.
• Flashback table operations are not supported for the SYS user.
• Current indexes and dependent objects, such as views, will be maintained.
• The Flasback Table operation will be written to the Alert.log.
• Constraints of the table will be checked. If the Operation violates any constraints, that it will be aborted.
• It does NOT cause the statistics of the table to be flashed back.

	Using Flashback Table
	ALTER SYSTEM SET UNDO_RETENTION = 2400; or ALTER TABLESPACE undotbs1 RETENTION GUARANTEE
	DELETE FROM tab_1 WHERE id = 5; COMMIT;
	ALTER TABLE tab_1 ENABLE ROW MOVEMENT;
	FLASHBACK TABLE tab_1 TO TIMESTAMP systimestamp – interval '15' minute [ENABLE TRIGGERS]; FLASHBACK TABLE tab_1 TO SCN 65444554 [ENABLE TRIGGERS];

Source: http://www.oraone.com/techblog/index.php?menu=1&begin=1&cikk=336&form=0