Sunday, November 3, 2013

How to use flashback table

Use a point-in-time in the past:

FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP
TO_TIMESTAMP('09-12-2008 14:00:00', 'DD-MM-YYYY HH24:MI:SS');
Notice the peculiar syntax:
"TO TIMESTAMP TO_TIMESTAMP..."
Use a specific SCN:
FLASHBACK TABLE SCOTT.EMP TO SCN 144003819;

If you want to flash back multiple tables, you can use many table names separated by a comma as follows:
FLASHBACK TABLE SCOTT.EMP, SCOTT.DEPT TO SCN 144003819;

Use a restore point:
FLASHBACK TABLE SCOTT.EMP TO RESTORE POINT BEFORE_MAINTENANCE;
Flashback to before drop:
FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP;

You can add the RENAME TO clause to rename the retrieved table:
FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP RENAME TO EMP_OLD;

Note:
For Oracle Flashback Table to succeed, the system must retain enough undo information to satisfy the specified SCN or timestamp, and the integrity constraints specified on the tables cannot be violated. Also, row movement must be enabled on the table.

Row movement must be enabled for the table you attempt to flash back. It can be switched on right before you use the flashback table feature. If you are flashing back to a time when row movement was *not* enabled, that is no problem, you can still use flashback table, as long as row movment is enabled *at the time* of executing the flashback table command.

There is one important exception to the requirement that row movement must be enabled: flashing back table TO BEFORE DROP. Such an operation Oracle calls a "flashback drop operation", and it uses dropped data in the recyclebin rather than undo data.

You can check the content of the recyclebin before you flash back a table to before it was dropped, like this:
select owner,object_name,original_name,operation,droptime,can_undrop from dba_recyclebin where original_name='EMP';

OWNER                          OBJECT_NAME                    ORIGINAL_NAME                  OPERATION DROPTIME            CAN
------------------------------ ------------------------------ ------------------------------ --------- ------------------- ---
SCOTT                  BIN$StmkAPSYpxbgUzwGAQph5w==$0 EMP               DROP      2017-03-16:14:00:09 YES



There are some restrictions to flashback table. Read about them here "Notes on Flashing Back Dropped Tables"

No comments:

Post a Comment