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 May 9, 2003

Recovering Accidentally Lost Data Using Oracle's Flashback Query - Page 2

By DatabaseJournal.com Staff

by Sreeram Surapaneni

Illustrations:

Having discussed the concepts behind the Flashback Query, let us now examine how it can be used in a real world environment. Let us take the example of an email-company where the set of email addresses was accidently deleted by the DBA from users table at 9:05AM. This mistake was later discovered at 11:30AM. If the company decides to rollback the database just prior to 9:05 AM, when the error occurred, in order to "recover" the deleted data, it would not only loose all subsequent transactions but also the system will be unavailable for the duration of the recovery. Clearly, this is not a solution for the service- company since it forces the company to compromise the system availability to serve the existing users in the system. Flashback query makes it possible for the company to correct the mistake without effecting normal operations as illustrated below.

Example 1
Using "AS OF" clause:

INSERT INTO USERS 
	(SELECT  * FROM USERS AS OF TIMESTAMP
	TO_TIMESTAMP ('22-APR-03 9:04:58','DD-MON-YY HH24: MI: SS')
	MINUS

	SELECT * FROM USERS);

Using "DBMS_FLASHBACK utility":

DECLARE
  CURSOR c IS
    SELECT *
    FROM   users;
v_rec  c%ROWTYPE;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_TIME ('22-MAR-03 09:04:58');
  OPEN c;
DBMS_FLASHBACK.DISABLE;
LOOP 
    FETCH c INTO v_row; 
    EXIT WHEN c%NOTFOUND; 
    INSERT INTO users VALUES
    (v_rec.user_id, v_rec.first_name, 
     v_rec.last_name, v_rec.email, 
     v_rec.phone_number, v_rec.address,
     ); 
  END LOOP; 
  CLOSE c;
  COMMIT;
END;


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