Quick Search:
 
 Oracle PL/SQL: FLASHBACK: AS OF clause Jump to:  
Category: >> Oracle PL/SQL >> FLASHBACK: AS OF clause  

<< lastnext >>

Snippet Name: FLASHBACK: AS OF clause

Description: When data is accidentally deleted, Flashback can allow the data to be resurrected in several different ways.

The first example shows how to use the AS OF clause for recovering data to a certain point in time (at which we know our data existed).

Another way of telling the system how far to go back is the use of SCN - System Change Number. The procedure is the same as the previous one, trying to recover lost data using the DBMS_FLASHBACK utility. The difference is that this time instead of using the time and date, we're using the System Change Number (SCN) to enter the Flashback mode. This SCN number can be obtained before the transaction is initiated by using the GET_SYSTEM_CHANGE_NUMBER function of the DBMS_FLASHBACK utility.

Also see:
» FLASHBACK: Oracle 10G enhancements
» FLASHBACK: SYS commands and queries
» FLASHBACK: Initialization Parameters
» FLASHBACK: Syntax Elements

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 13th, 2009

INSERT INTO EMPLOYEE_TEMP
(SELECT * 
FROM EMPLOYEE 
AS OF TIMESTAMP ('13-SEP-04 8:50:58','DD-MON-YY HH24: MI: SS'));
 
 
-- alternate date format (mm/dd/yyy)
INSERT INTO EMPLOYEE_TEMP
(SELECT *
FROM EMPLOYEE
AS OF TIMESTAMP
TO_TIMESTAMP('10/04/2008 11:00:00', 'MM/DD/YYYY HH24:MI:SS'));
 
 
 
-- using the System Change Number:
-- obtain the System Change Number before the transaction is 
-- initiated by using the GET_SYSTEM_CHANGE_NUMBER function 
-- of the DBMS_FLASHBACK utility:
 
SELECT DBMS_FLASHBACK. GET_SYSTEM_CHANGE_NUMBER FROM dual;
 
 
-- recover the data using the SCN number:
 
INSERT INTO EMPLOYEE_TEMP
(SELECT * FROM EMPLOYEE AS OF SCN 10280403339);
 
 
 
-- Using the DBMS_FLASHBACK package (prior to Oracle 9i release 2):
-- use the ENABLE_AT_TIME function of the DBMS_FLASHBACK package 
-- to enter the flashback mode, followed by a DISABLE function to 
-- resume normal operation:
 
 DECLARE
   CURSOR emp_cur IS
    SELECT * FROM   EMPLOYEE;
  v_rec emp_cur%ROWTYPE;   
 BEGIN
     DBMS_FLASHBACK.ENABLE_AT_TIME ('13-SEP-04 08:10:58');
     OPEN emp_cur;
     DBMS_FLASHBACK.DISABLE;
    LOOP
     FETCH emp_cur INTO v_rec; 
     EXIT WHEN emp_cur%NOTFOUND; 
   INSERT INTO EMPLOYEE_TEMP VALUES
    (v_rec.emp_id,
     v_rec.name, 
     v_rec.age ); 
  END LOOP; 
 CLOSE emp_cur;
   COMMIT;
 END;
 
 
-- to use the SCN instead, change the ENABLE_AT_TIME line to this:
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (10280403339);
 
/*
Notice that once we have entered into the flashback query mode, 
DML statements INSERT, UPDATE, or DELETE are not allowed 
until we exit flashback query mode by issuing 
DBMS_FLASHBACK.DISABLE. Because of this limitation the cursor 
for loop cannot be used in using the DBMS_FLASHBACK if we are 
using any of the above DML statements inside the loop.
 
Also, Flashback mode can be entered only at the beginning of a 
transaction. If a DML statement has been issued, it must be
committed before we can enter the flashback mode.
*/
 


 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org