Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints. You do not have to restore any data from backups
Using Flashback Table
Row movement must be enabled on the table. Use following SQL statement:
ALTER TABLE tablename ENABLE ROW MOVEMENT;
- You must have FLASHBACK ANY TABLE or FLASHBACK object privilege on the table.
- You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
Performing Flashback Table
The EMP table is restored to its state when the database was at the time specified by the SCN.
FLASHBACK TABLE EMP TO SCN 123456;
You can also specify the target point in time for the FLASHBACK TABLE operation using TO_TIMESTAMP. For example:
FLASHBACK TABLE EMP TO TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
Mapping of timestamps to SCNs
The mapping of timestamps to SCNs is not always exact. When using timestamps with the FLASHBACK TABLE statement, the actual point in time to which the table is flashed back can vary by up to approximately three seconds of the time specified for TO_TIMESTAMP. If an exact point in time is required, use an SCN rather than a time expression.
By default, the database disables triggers during FLASHBACK TABLE operation, and after operation enabled or disabled again. If you wish to enable triggers during FLASHBACK TABLE, use following statement:
FLASHBACK TABLE table_name
TO TIMESTAMP timestamp ENABLE TRIGGERS;
FLASHBACK TABLE EMP
TO TIMESTAMP TO_TIMESTAMP('2005-03-03 14:00:00') ENABLE TRIGGERS;