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