Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 17, 2003

Using Flashback in Oracle 9i

By DatabaseJournal.com Staff


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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date