16
DBMS_FLASHBACK

Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN). When DBMS_FLASHBACK is enabled, the user session uses the Flashback version of the database, and applications can execute against the Flashback version of the database. DBMS_FLASHBACK is automatically turned off when the session ends, either by disconnection or by starting another connection.

PL/SQL cursors opened in Flashback mode return rows as of the flashback time or SCN. Different concurrent sessions (connections) in the database can perform Flashback to different wall-clock times or SCNs. DML and DDL operations and distributed operations are not allowed while a session is running in Flashback mode. You can use PL/SQL cursors opened before disabling Flashback to perform DML.

Under Automatic Undo Management (AUM) mode, you can use retention control to control how far back in time to go for the version of the database you need. If you need to perform a Flashback over a 24-hour period, the DBA should set the undo_retention parameter to 24 hours. This way, the system retains enough undo information to regenerate the older versions of the data.

When enabling Flashback using a wall-clock time, the database chooses an SCN that was generated within five minutes of the time specified. For finer grain control of Flashback, you can enable an SCN. An SCN identifies the exact version of the database. In a Flashback-enabled session, SYSDATE will not be affected; it will continue to provide the current time.

DBMS_FLASHBACK can be used within logon triggers to enable Flashback without changing the application code.

You may want to use DBMS_FLASHBACK for the following reasons:

Self-service repair. If you accidentally delete rows from a table, you can recover the deleted rows.
Packaged applications such as e-mail and voicemail. You can use Flashback to restore deleted e-mail by re-inserting the deleted message into the current message box.
Decision support system (DSS) and online analytical processing (OLAP) applications. You can perform data analysis or data modeling to track seasonal demand, for example.

To use this package, a database administrator must grant EXECUTE privileges for DBMS_FLASHBACK.

See Also:

Oracle9i Application Developer's Guide - Fundamentals for detailed information about DBMS_FLASHBACK.

This chapter discusses the following topics:

DBMS_FLASHBACK Error Messages

Table 16-1 DBMS_FLASHBACK Error Messages

Error	Description
8182	In Flashback mode, user cannot perform DML or DDL operations.
8184	User cannot enable Flashback within another Flashback session.
8183	User cannot enable Flashback within an uncommitted transaction.
8185	SYS cannot enable Flashback mode. User cannot begin read only or serializable transactions in Flashback mode.
8180	Time specified is too old.
8181	Invalid system change number specified.

DBMS_FLASHBACK Example

The following example illustrates how Flashback can be used when the deletion of a senior employee triggers the deletion of all the personnel reporting to him. Using the Flashback feature, you can recover and re-insert the missing employees.

/* keep_scn is a temporary table to store scns that we are interested in. */
create table keep_scn (scn number);
execute dbms_flashback.disable;
set echo on
create table employee (

employee_no   number(5) primary key,
employee_name varchar2(20),
employee_mgr  number(5)


constraint mgr_fkey references employee on delete cascade,


salary        number,
hiredate      date

);

/* Populate the company with employees */
insert into employee values (1, 'John Doe', null, 1000000, '5-jul-81');
insert into employee values (10, 'Joe Johnson', 1, 500000, '12-aug-84');
insert into employee values (20, 'Susie Tiger', 10, 250000, '13-dec-90');
insert into employee values (100, 'Scott Tiger', 20, 200000, '3-feb-86');
insert into employee values (200, 'Charles Smith', 100, 150000, '22-mar-88');
insert into employee values (210, 'Jane Johnson', 100, 100000, '11-apr-87');
insert into employee values (220, 'Nancy Doe', 100, 100000, '18-sep-93');
insert into employee values (300, 'Gary Smith', 210, 75000, '4-nov-96');
insert into employee values (310, 'Bob Smith', 210, 65000, '3-may-95');
commit;

/* Show the entire org */
select lpad(' ', 2*(level-1)) || employee_name Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no = 1
order by level;

execute dbms_flashback.disable;
/* Store this snapshot for later access through Flashback */
declare
I number;
begin
I := dbms_flashback.get_system_change_number;
insert into keep_scn values (I); 
commit;

/* Scott decides to retire but the transaction is done incorrectly */

delete from employee where employee_name = 'Scott Tiger';
commit;

/* notice that all of scott's employees are gone */
select lpad(' ', 2*(level-1)) || employee_name Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no = 1
order by level;

/* Put back scott's organization */
declare

restore_scn date;

begin

select  scn into restore_scn from keep_scn;
dbms_flashback.enable_at_system_change_number (restore_scn);

end;
/

/* Show Scott's org */
select lpad(' ', 2*(level-1)) || employee_name Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no =

(select employee_no from employee where employee_name = 'Scott Tiger')

order by level;

declare

scotts_emp number;
scotts_mgr number;
cursor c1 is


select employee_no, employee_name, employee_mgr, salary, hiredate
from employee
connect by prior employee_no = employee_mgr
start with employee_no = 


(select employee_no from employee where employee_name = 'Scott 
Tiger'); 

c1_rec is c1 % ROWTYPE;
begin

select employee_no, employee_mgr into scotts_emp, scotts_mgr from employee
where employee_name = 'Scott Tiger';

/* Open c1 with Flashback enabled. */
 open c1;  
/* Disable Flashback. */
dbms_flashback.disable;
 loop
 /* Note that all the DML operations inside the loop are performed with 
Flashback disabled. */

fetch c1 into c1_rec;

exit when c1%NOTFOUND;

for c1_rec in c1 loop
if (c1_rec.employee_mgr = scotts_emp) then


insert into employee values (c1_rec.employee_no,


c1_rec.employee_name,
scotts_mgr,
c1_rec.salary,
c1_rec.hiredate);

else

if (c1_rec.employee_no != scotts_emp) then
insert into employee values (c1_rec.employee_no, 


c1_rec.employee_name,
c1_rec.employee_mgr,
c1_rec.salary,
c1_rec.hiredate);

      end if;
    end if;
end loop;
end;
/

execute dbms_flashback. disable;

Summary of DBMS_FLASHBACK Subprograms

Table 16-2 DBMS_FLASHBACK Subprograms

Subprogram	Description
"ENABLE_AT_TIME Procedure"	This procedure enables Flashback for the entire session. The snapshot time is set to the SCN that most closely matches the time specified in `query_time.`
"ENABLE_AT_SYSTEM_CHANGE_NUMBER Procedure"	This procedure takes an SCN as an Oracle number and sets the session snapshot to the specified number. Inside the Flashback mode, all queries will return data consistent as of the specified wall-clock time or SCN.
"GET_SYSTEM_CHANGE_NUMBER Function"	This function returns the current SCN as an Oracle number. You can use the SCN to store specific snapshots.
"DISABLE Procedure"	This procedure disables the Flashback mode for the entire session.

16DBMS_FLASHBACK