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.

Triggers State

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;
Go to top
JSN Boot template designed by JoomlaShine.com