Oracle Flashback Query

Summary

Oracle Flashback Query feature utilizes the undo segments within the undo tablespace to execute a query as the query would have returned at a prior point in time using a timestamp or SCN number. This feature is powerful in the event of erroneously delete or update rows. How far back to a previous point in time Flashback Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.


Executing a Flashback Query
Procedure assumes that the flashback capabilities were setup and tuned as outline in Setup Oracle Transactional Flashback Capability (Automatic Undo)

Simple examples 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.


Delete Example

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/21/2009 09:53:21

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.


2. Next step is to identify what prior point in time the table has the records you are looking for. This will more then likely require input from the customer that reporting the missing data.
In the example case we will want to go back before 01/21/2009 9:53am.

3. Execute the query with flashback syntax using a timestamp

SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 09:53: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

OR

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

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.

** As you can see the rows deleted are there……


4. Execute Repair and put the deleted rows back

SQL> insert into scott.emp values ('7369','SMITH','CLERK','7902', TO_DATE('17-DEC-1980','DD-MON-YYYY'),800,NUL
L,20) ;

1 row created.

SQL> insert into scott.emp values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-1981','DD-MON-YYYY'),2450,N
ULL,10) ;

1 row created.

SQL> commit ;

Commit complete.





Update Example

Setting up for the example we will update 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/22/2009 10:36:18

SQL> update scott.emp set sal=10000 where empno=7369 ;

1 row updated.

SQL> update scott.emp set sal=10000 where empno=7782 ;

1 row updated.

SQL> commit ;

Commit complete.


1. Verify the data is updated

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 10000 20
7782 CLARK MANAGER 7839 09-JUN-81 10000 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.


2. Next step is to identify what prior point in time the table has the records correctly. This will more then likely require input from the customer that reporting the missing data.

3. Execute the query with flashback syntax using a timestamp

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

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
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.

OR Show only the rows that are different.

SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 10:36:00','mm/dd/yyyy hh24:mi:ss')
2 minus
3 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


4. Execute Repair and put the deleted rows back

SQL> update scott.emp set sal=800 where empno=7369 ;

1 row updated.

SQL> update scott.emp set sal=2450 where empno=7782 ;

1 row updated.

SQL> commit ;

Commit complete.