Databases Reference
In-Depth Information
2.
If the output comes back as DISABLED, enable it by issuing this SQL statement:
SQL> alter table accounts enable row movement;
Table altered.
This prepares the table for flashback.
3.
Check the table to see how far into the past you can flash it back. Use Recipe 13-6 to
determine how far back into the past you can go.
4.
Flash the table back to a specific timestamp:
SQL> flashback table accounts to timestamp to_date ('12-JUL-12 18.23.00', 'dd-MON-YY hh24.mi.ss');
Flashback complete.
You can flash back to a specific SCN as well:
5.
Check the data in the table to make sure you have flashed back to the exact point you want.
If the flashback was not enough, you can flash the table back once more to a point even
further in the past. For instance, the previous step reinstated the table as of 6:23 p.m.,
which was not enough. In this step, you will flash it back to one more minute in the
past—to 6:22 p.m.
SQL> flashback table accounts to timestamp to_date ('12-JUL-12 18.22.00',
'dd-MON-YY hh24.mi.ss');
Flashback complete.
6.
If you have gone too far into the past, you can flash “forward” using the same flashback
statement:
SQL> flashback table accounts to timestamp to_date ('12-JUL-12 18.24.00',
'dd-MON-YY hh24.mi.ss');
Flashback complete.
As you can see, you can flash the table back and forth until you arrive at the exact point.
The flashback is complete. Since the table was not dropped, all dependent objects, such as triggers and indexes,
remain unaffected.
How It Works
Table flashback is entirely different from the database flashback you saw earlier in the chapter. When a table's data
changes, the past information is stored in undo segments. Oracle uses this information to present a read-consistent
view of the data later. Even if the changes were committed, the undo data is important for the read-consistent image
needed by a query that started after the data was changed but before it was committed.
