Flashback Table - Procedure

Step 1: Make sure that the table has row movement enabled.

SQL> Select row_movement From user_tables where table_name=’EMP’;

Step 2: Enable Row movement if it is disabled. If enabled ignore this step.

SQL> Alter Table emp Enable Row Movement;

Step 3: Check how far back you can flash back.

SQL> Select Oldest_flashback_SCN, to_char(oldest_flashback_time, ‘MM/DD/YY HH24:MI:SS’) from v$flashback_database_log;

Step 4: Flashback the table to a specific time stamp.

SQL> Flashback table emp to timestamp to_date (’28-Mar-2009 16:25:00’, ‘dd-Mon-yyyy hh24:mi:ss’);

Note: You can flashback to a specific SCN also.

Note: If the flashback was not enough you can flash back once more. For eg to 16:23:00. Also if you have gone too far then you can go forward using the same flashback statement to 16:24:00. You can flash the table back and forth until you arrive at the exact point.

Thanks