If a table was erroneously deleted from, you have the option of flashing back the table to a previous point in time.

The Flashback Table feature uses information in the undo tablespace to restore the table.

The point in time in the past depends on your undo tablespace retention period, which specifies the minimum time that undo information is kept.

If the required flashback information is not in the undo tablespace, you receive an error such as this:

ORA-01555: snapshot too old

In other words, to be able to flash back to a point in time in the past, the required information in the undo tablespace must not have been overwritten.

FLASHBACK TABLE TO SCN

Suppose you are testing an application feature, and you want to quickly restore a table back to a specific SCN.

As part of the application testing, you record the SCN before testing begins:

SQL> select current_scn from v$database; CURRENT_SCN

———–4760099

You perform some testing and then want to flash back the table to the SCN previously recorded.

First, ensure that row movement is enabled for the table:

SQL> alter table inv enable row movement; SQL> flashback table inv to scn 4760089;

The table should now reflect transactions that were committed as of the historical SCN value specified in the FLASHBACK statement.

FLASHBACK TABLE TO TIMESTAMP

You can also flash back a table to a prior point in time. For example, to flash back a table to 15 minutes in the past, first enable row movement, and then use FLASHBACK TABLE:

SQL> alter table inv enable row movement;

SQL> flashback table inv to timestamp(sysdate-1/96);

The timestamp you provide must evaluate to a valid format for an Oracle timestamp. You can also explicitly specify a time, as follows:

SQL> flashback table inv to timestamp

to_timestamp(’14-jun-23 12:07:33′,’dd-mon-yy hh24:mi:ss’);

FLASHBACK TABLE TO RESTORE POINT

A restore point is a name associated with a timestamp or an SCN in the database. You can create a restore point that contains the current SCN of the database, as shown here:

SQL> create restore point point_a;

Later, if you decide to flash back a table to that restore point, first enable row movement:

SQL> alter table inv enable row movement;

SQL> flashback table inv to restore point point_a;

The table should now contain transactions as they were at the SCN associated with the specified restore point.