Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: FLASHBACK: AS OF clause Jump to:  
Category: >> Oracle PL/SQL >> FLASHBACK: AS OF clause Bookmark and Share

<< 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.
*/
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 114 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?