Using DBMS_FLASHBACK in Oracle to set your entire session back in time

Posted by David Yahalom under Oracle

Last week I’ve talked to you about using Flashback query, Flashback table and FlashBack database in Oracle 10g to help you improve productivity and perhaps save you time in doing complete or partial database recovery.

Today I want to talk to you about a less known feature of Flashback, the DBMS_FLASHBACK package available since Oracle 9iR1.

The DBMS_FLASHBACK package provides almost the same functionality as FlashBack query but with one big difference. Instead of being able to “go back in time” one query at a time, the DBMS_FLASHBACK package acts as a time machine, or a time tunnel, allowing you to “turn back the clock” and carry out normal SQL queries or PL/SQL code, without change, but as if you were running them from a time in the past.

Flashback query in Oracle 9iR2 (using the as of clause in your SQL statement) is actually based and internally using the capabilities of DBMS_FLASHBACK package.

To use DBMS_FLASHBACK you simply call DBMS_FLASHBACK.ENABLE_AT_TIME (and specify a specific timestamp in the database life cycle) or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (and specify an SCN number in the database life cycle) to “set back the clock” of your current session as a whole.

Following that you can run you queries and PL/SQL code normally without any modifications but instead of getting the results you normally would (which represent to current state of the data in your database) the results you receive represent the state of the database as it was at the timestamp you provided with the DBMS_FLASH.ENABLE_AT_TIME command.

To return to the “present” simply cancel the “time tunnel” using the DFBMS_FLASHBACK.DISABLE command.

Let’s write an example that will show us just how easy it is to use the DBMS_FLASHBACK package.

SQL> conn admin/*********@daviddb
Connected.
SQL> create table employees (ename char(20), sal number(6));

Table created.

SQL> insert into employees values ('Linus Torvalds', 5000);

1 row created.

SQL> insert into employees values ('Tom Kyte', 4000);

1 row created.

SQL> insert into employees values ('Steve Ballmer', 9000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             5000
Tom Kyte                   4000
Steve Ballmer              9000
This is our base table of employees. At this time the table holds "good" data.

SQL> var sysscn number
SQL> EXEC :sysscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER

PL/SQL procedure successfully completed.

I’m creating a sysscn variable that I will use to store the current SCN of the system. In this example, using a system SCN number as the reference to which we will later Flashback to is easier to demonstrate than using a timestamp.

SQL> update employees set sal = 9999;

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * From employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             9999
Tom Kyte                   9999
Steve Ballmer              9999

An employee from the HR department has accidentally updated all employee salaries in the company to 9999. When he notices this mistake he notifies the DBA immediately that something is wrong with employee salaries. Normally, this would require us to recover that table from a backup. We can avoid this by using the DBMS_FLASHBACK package.

SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:sysscn);

PL/SQL procedure successfully completed.

SQL> select * from employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             5000
Tom Kyte                   4000
Steve Ballmer              9000

SQL> exec dbms_flashback.disable

PL/SQL procedure successfully completed.

Remember that while you are in a “time tunnel” you cannot use DML or DDL statements and that you cannot open a “time tunnel” from within a “time tunnel”. That is, you must use

Also keep in mind that you must have the execute privilege on the DBMS_FLASHBACK package in order to use this wonderful feature.

Using DBMS_FLASHBACK will sometimes make your life easier than using a regular Flashback query. Because it sets your entire session “back in time” you can run multiple database-wide queries or execute PL/SQL code freely from your session without restriction or the need to re-write existing code with the as of clause.