Oracle Flashback Table

The FLASHBACK TABLE command can be used to restore an earlier state of a table in the event of human or application error.

Restoring a dropped table

The first use of FLASHBACK TABLE is to retrieve a table from the recycle bin after it has been dropped.

DROP TABLE dept;

Table dropped.

SELECT *
FROM dept;

SQL Error: ORA-00942: table or view does not exist

SELECT object_name, original_name
FROM recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    
------------------------------ -------------------------------- 
BIN$QzJJ24I1hx7gQAAK+wFg5Q==$0 DEPT

1 rows selected

FLASHBACK TABLE dept TO BEFORE DROP;

Flashback complete.

SELECT *
FROM dept;

DEPTNO                 DNAME          LOC           
---------------------- -------------- ------------- 
10                     ACCOUNTING     NEW YORK      
20                     RESEARCH       DALLAS        
30                     SALES          CHICAGO       
40                     OPERATIONS     BOSTON

4 rows selected

The RENAME TO clause can be used to give the table a new name during the restore. This is useful if a new table has been created with the same name since the old one was deleted.

FLASHBACK TABLE dept TO BEFORE DROP RENAME TO dept_before_drop;

Flashback complete.

Any indexes or triggers associated with the table will still have their recycled names and will need to be re-instated manually. Also, views and procedures which rely on the table will have been invalidated and will need to be recompiled.

Restoring a table to an earlier state

FLASHBACK TABLE can also be used to revert the state of a table back to a specified time or SCN.

Let's see this in action using the EMP table in the Oracle demo schema.

SELECT deptno, count(*)
FROM emp
GROUP BY deptno;

DEPTNO       COUNT(*)               
------------ --------------
10           3                      
20           5                      
30           6

3 rows selected

Suppose we wish to delete all entries for department 10 but we mistype the SQL and commit before we notice our mistake.

DELETE FROM emp
WHERE deptno >= 10;

14 rows deleted

COMMIT;

Commit complete.

SELECT deptno, count(*)
FROM emp
GROUP BY deptno;

DEPTNO       COUNT(*)               
------------ --------------

0 rows selected

All the rows have been deleted and the transaction committed. But fear not, flashback technology was introduced to deal with just this kind of problem. We can restore the table back to the state it was in, say, one minute ago. Before we can do this, however, we must ensure that row movement is enabled on the table.

ALTER TABLE emp ENABLE ROW MOVEMENT;

Table altered.

FLASHBACK TABLE emp TO TIMESTAMP systimestamp - INTERVAL '1' MINUTE;

Flashback complete.

SELECT deptno, count(*)
FROM emp
GROUP BY deptno;

DEPTNO       COUNT(*)               
------------ --------------
10           3                      
20           5                      
30           6

3 rows selected

The table has been 'rewound' to a prior point in time. All the rows that existed on the table one minute ago have been restored. It is as if the delete never happened.

Alternatively, we could restore to a particular SCN. To use this method we must know the exact SCN we wish to restore to, so we will need to record the value before the update is attempted.

SELECT current_scn
FROM v$database;

CURRENT_SCN            
---------------------- 
3779819703

1 rows selected

DELETE FROM emp
WHERE deptno >= 10;

14 rows deleted

COMMIT;

COMMIT succeeded.

FLASHBACK TABLE emp TO SCN 3779819703;

FLASHBACK TABLE succeeded.

SELECT deptno, count(*)
FROM emp
GROUP BY deptno;

DEPTNO       COUNT(*)               
------------ --------------
10           3                      
20           5                      
30           6

3 rows selected

Restore Point

Oracle 10g Release 2 introduced the concept of restore points. A restore point is a name which is associated with the SCN of the database at the time the restore point was created. We can reference a restore point name in the FLASHBACK TABLE command.

For example.

CREATE RESTORE POINT before_changes;

Restore point created.

If we run in to any problems during our updates, we can simply rewind one or more tables back to the restore point.

DELETE FROM emp
WHERE deptno >= 10;

14 rows deleted

COMMIT;

COMMIT succeeded.

FLASHBACK TABLE emp TO RESTORE POINT before_changes;

FLASHBACK TABLE succeeded.

SELECT deptno, count(*)
FROM emp
GROUP BY deptno;

DEPTNO       COUNT(*)               
------------ --------------
10           3                      
20           5                      
30           6

3 rows selected

Restrictions

There are are some rules and restrictions to be aware of when using FLASHBACK TABLE.

• This feature is only available with Oracle Enterprise Edition.
  
• You will need the appropriate privileges (such as FLASHBACK ANY TABLE) to execute these commands. See the Oracle documentation for more details.
  
• Oracle may restore rows to a different location and allocate new rowids. Any references to the original rowids will no longer be valid.
  
• There must be sufficient redo data available in the database to restore the data. This can be guaranteed for a predetermined period of time if the UNDO_RETENTION initialization parameter is used.
  
• You cannot restore a table to an earlier state across any DDL operations that change the structure of the table (including TRUNCATE).
  
• Restore points were only introduced at 10g Release 2.