0
FLASHBACK TABLE
Use the
FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
Prerequisites
To flash back a table to an earlier SCN or timestamp, you must have either the
FLASHBACK object privilege on the table or the FLASHBACK ANYTABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table.
Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table
TO BEFORE DROP. That operation is called a flashback drop operation, and it uses dropped data in the recyclebin rather than undo data. Please refer to row_movement_clausefor information on enabling row movement.
To flash back a table to a restore point, you must have the
SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or theSELECT_CATALOG_ROLE role.
To flash back a table to before a
DROP TABLE operation, you need only the privileges necessary to drop the table.
Restrictions on Flashing Back Tables This statement is subject to the following restrictions:
- Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
- The following DDL operations change the structure of a table, so that you cannot subsequently use the
TOSCNorTOTIMESTAMPclause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
TO SCN Clause
Specify the system change number (SCN) corresponding to the point in time to which you want to return the table. The
expr must evaluate to a number representing a valid SCN.SELECT current_scn FROM v$database;
Specify a timestamp value corresponding to the point in time to which you want to return the table. The
expr must evaluate to a valid timestamp in the past. The table will be flashed back to a time within approximately 3 seconds of the specified timestamp.
Specify a restore point to which you want to flash back the table. The restore point must already have been created.
FLASHBACK QUERY EXAMPLE:
SELECT COUNT(*)
FROM TableName AS OF TIMESTAMP TO_TIMESTAMP
('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');
SELECT COUNT(*)
FROM TableName AS OF SCN 722452;
FLASHBACK TABLE EXAMPLE: ALTER TABLE TableName ENABLE ROW MOVEMENT; FLASHBACK TABLE TableName TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS'); FLASHBACK TABLE TableName TO SCN 715315;