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

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;