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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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











×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.