Using Flashback in Oracle 9i


Ajay Gursahani

Oracle
9i’s Flashback feature, DBMS_FLASHBACK, provides the user with the ability to view data in a database “as it was,” at a specified point in the past, even though committed
transactions have since been applied.

The
subprograms available under DBMS_FLASHBACK package are listed below:

ENABLE_AT_TIME

ENABLE_AT_TIME
is used to ‘flashback’ to a specified time for which to view the database.
Flashback is enabled for the entire session.


DBMS_FLASHBACK.ENABLE_AT_TIME (query_time IN TIMESTAMP);

query_time

This is an input parameter, which accepts TIMESTAMP as an input value.

Example:


execute dbms_flashback.enable_at_time(‘2002-12-10 10:00:00’);

or you can use the TO_TIMESTAMP function:


execute dbms_flashback.enable_at_time(TO_TIMESTAMP
(’15-03-2002 22:00:00′,’DD-MM-YYYY HH24:MI:SS’));

In
the example above, we are setting the time to 15th March 2002 10:00PM

ENABLE_AT_SYSTEM_CHANGE_NUMBER

The
ENABLE_AT_SYSTEM_CHANGE_NUMBER is used to
‘flashback’ to a point, specified by SCN (System Change Number), from which you
want to view the database. It enables Flashback for the entire session.


DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (query_scn IN NUMBER);

query_scn

query_scn
is an input parameter that accepts a numeric value in the form of SCN (System
Change Number). SCN is generated every time a transaction is committed.

Example:


execute dbms_flashback.enable_at_system_change_number(100);

To
view from the 100th transaction and on of a database, specify SCN of
100.

GET_SYSTEM_CHANGE_NUMBER

The
GET_SYSTEM_CHANGE_NUMBER function returns the current SCN. The datatype is an
Oracle NUMBER type; once obtained, the SCN can be stored for later use. This is
very useful if we want to take snapshots.


DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER( RETURN NUMBER);

DISABLE

This
procedure disables the Flashback mode for the entire session.


DBMS_FLASHBACK.DISABLE;

Example:


EXECUTE dbms_flashback.enable_at_time(TO_TIMESTAMP
(’12-DEC-2002 10:00:00′,’DD-MON-YYYY HH24:MI:SS’));
SELECT * FROM emp WHERE empno=5678;
EXECUTE dbms_flashback.disable;

In this example, the flashback session has been
enabled from 12th
Dec 2002
at 10:00AM.

Next, we query emp table and later disable
flashback for the entire session.

Please note:

  1. Use the Flashback option only if Oracle Server is configured to use Automatic Undo Management. Set UNDO_MANAGEMENT parameter in init.ora to AUTO
  2. We have to define a parameter in init.ora file, which will set UNDO_RETENTION period (in seconds). The default period is 900 seconds.
  3. Use READ-ONLY operations only when the Flashback is enabled, i.e. DML or DDL statements cannot be executed.
  4. FLASHBACK time specified by ENABLE_AT_TIME procedure should be less than 5 minutes from the current system time.
  5. FLASHBACK cannot be used in the middle of a transaction
  6. An UNDO TABLESPACE must be created when using Automatic Undo Management mode.

A typical example of the use of Flashback
would be if we have an ITEM_MASTER table and all the records are accidentally
deleted around 4:00PM. We can FLASHBACK to 3:50pm, a few minutes before
DELETE took place, and populate ITEM_MASTER table.

Example I:


DECLARE
CURSOR cur_item_mst is
SELECT * FROM item_master;
v_row_item_mst item_master%ROWTYPE;
BEGIN
Dbms_flashback.enable_at_time(TO_TIMESTAMP
(’20-DEC-2002 15:50:00′,’DD-MON-YYYY HH24:MI:SS’));

Open cur_item_mst;
Dbms_flashback.disable;
Loop
Fetch cur_item_mst INTO v_row_item_mst;
Exit when cur_item_mst%NOTFOUND;
INSERT INTO item_master VALUES (
v_row_item_mst.itemno,
v_row_item_mst.description,
v_row_item_mst.rate);
END LOOP;
CLOSE cur_item_mst;
COMMIT;
END;
/

Example II:

SELECT to_char(sysdate,’DD-MON-YY HH24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’D
——————
26-DEC-02 10:00:00

SQL> SELECT * FROM test;

UNIQUE_ID NAME SALARY
————— ————— ———–
1 ANDY 4500.00
2 ALAN 3500.00
3 JACK 3600.00
4 PETER 4000.00
5 JOE 2900.00

5 rows selected

After approx 20 minutes…

SELECT to_char(sysdate,’DD-MON-YY HH24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’D
——————
26-DEC-02 10:20:00

SQL> SELECT * FROM test;

UNIQUE_ID NAME SALARY
————— ————— ———–
1 ANDY 4500
2 ALAN 3500
3 JACK 3600
4 PETER 4000
5 JOE 2900

5 rows selected

SQL> UPDATE test SET SALARY=10000;

5 rows updated

SQL> SELECT * FROM test;

UNIQUE_ID NAME SALARY
————— ————— ———–
1 ANDY 10000
2 ALAN 10000
3 JACK 10000
4 PETER 10000
5 JOE 10000

5 rows selected

Now if we FLASHBACK to 10:10:00 we will get the data as
it was prior to the UPDATE statement:

SQL>execute dbms_flashback.enable_at_time(TO_TIMESTAMP
(’26-DEC-02 10:10:00′,’DD-MON-YY HH24:MI:SS’));

PL/SQL procedure successfully completed

SQL> SELECT * FROM test;

UNIQUE_ID NAME SALARY
————— ————— ———–
1 ANDY 4500
2 ALAN 3500
3 JACK 3600
4 PETER 4000
5 JOE 2900

5 rows selected

This article has discussed what FLASHBACK is, and its uses–viewing past data or recovering lost data.

Latest Articles