Last week the need for flashback query finally came, and it worked like a charm. The client actually IM'd (not really a word, but you know what I mean) with the problem, and we IM'd through the solution using flashback query.

Flashback query became available with Oracle9i, but the specific feature I am going to use, the as of clause, was not available until Oracle9ir2. In this example, I am going to delete the data in the emp table in the scott schema, commit the change, and then insert the data back into the table using flashback query.

This is the data that is currently in the emp table.

SELECT empno, ename

FROM   emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Now I delete the data and commit it.

DELETE emp;

14 rows deleted.

COMMIT;

Commit complete.

Oh no! Someone deleted all my employees!

SELECT empno, ename

FROM   emp;

no rows selected

Flashback query to the rescue. To see the deleted data, I add the as of clause to the query. So this query is retrieving data from the emp table as it existed at 4:55pm, about  20 minutes ago.

SELECT empno, ename
FROM   emp
AS OF TIMESTAMP TO_TIMESTAMP ('01-FEB-2006 16:55:00', 'DD-MON-YYYY HH24:MI:SS');

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

But I don't want to just see the deleted data, I want to put it back into the emp table. So my query becomes part of an insert statement.

INSERT INTO emp
(SELECT *
FROM    emp
AS OF TIMESTAMP TO_TIMESTAMP ('01-FEB-2006 16:55:00', 'DD-MON-YYYY HH24:MI:SS'));

14 rows created.

COMMIT;

Commit complete.

And now I am back in business with all my employees.

SELECT empno, ename
FROM   emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Please note:

• This feature is available in both Standard and Enterprise Edition databases.

• Enabling flashback query requires the use of an undo tablespace. The following initialization parameters must be set.
  undo_management = AUTO (let Oracle manage undo using an undo tablespace)
  undo_retention = 900 (number of seconds undo information should be retained. Affects how far back you can go with flashback query and the size of the undo tablespace)
  undo_tablespace = UNDOTBS1 (undo tablespace name)

• The user scott required no additional privileges to use flashback query against tables in his own schema.

• You can use time or SCN with flashback query. If you use time, Oracle actually converts time to an SCN using data in the table SYS.SMON_SCN_TIME. That table contains 1440 rows and in my test database the timestamps are 5 to 10 minutes apart. The result is that changes made within those intervals may not be recoverable using flashback query by time.

Note: This tip was tested using Oracle9ir2.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.