Oracle Flashback Table

Summary

Oracle Flashback Table provides the capability to essentially recover a single table or multiple tables to a prior specified point in time without taking impacting any part of the databases availability. Flashback Table allows the contents of one or more individual tables to revert to their state at some past SCN or time. Flashback Table can eliminate the need to perform auxiliary point-in-time recovery operations. Flashback Table can restore tables to a prior point in time while maintaining all associated indexes, triggers and constraints. This is most valuable when a large amount of data in the table has been removed or updated by a process and you want to bring the table back to the state prior to the process execution.

Flashback Table uses the undo tablespace block data to execute the restoration of the table. You do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.

Flashback Table does however require row movement to be enabled and the flashback object privilege or the flashback any table system privilege in addition to select, insert, update, delete and alter permissions on the table.

** Note: This is a permanent process, once the table is flashed back you can not bring it back forward again, therefore be careful about the flash back table operations you perform.


Performing Flashback Table
Simple example will utilize the scott.emp table with the following records:

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.



Setting up for the example we will remove some rows from the scott.emp table.

SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;

TO_CHAR(SYSDATE,'MM
-------------------
01/23/2009 08:38:01

SQL> delete from scott.emp where empno = 7369 ;

1 row deleted.

SQL> delete from scott.emp where empno = 7782 ;

1 row deleted.

SQL> commit ;

Commit complete.


1. Verify the data is missing. In the example setup above we will check scott.emp for the missing records.

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

12 rows selected.

Or a way to show what is missing or updated using a flashback query.

SQL> select * from scott.emp as of timestamp to_timestamp('01/23/2009 08:38:00','mm/dd/yyyy hh24:mi:ss') minus select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10


2. Identify the time or SCN the table has to go back to.

3. Flashback the table


SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

Table altered.

SQL> FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP
2 TO_TIMESTAMP('2009-01-23 08:38:00', 'YYYY-MM-DD HH24:MI:SS')
3 ;

Flashback complete.


4. Check the table and verify it is back to prior state.

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.


5. Show that the operation can not be rolled back.

SQL> rollback ;

Rollback complete.

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.


6. Even if we exit the session and log back in the changes are still in place

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus mrmessin@orcl

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 23 08:56:43 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.