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