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.
Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 13th, 2009
ASOFTIMESTAMP('13-SEP-04 8:50:58','DD-MON-YY HH24: MI: SS'));-- alternate date format (mm/dd/yyy)INSERTINTO EMPLOYEE_TEMP
ASOFTIMESTAMPTO_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:INSERTINTO EMPLOYEE_TEMP
(SELECT*FROM EMPLOYEE ASOF 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:DECLARECURSOR emp_cur ISSELECT*FROM EMPLOYEE;
DBMS_FLASHBACK.ENABLE_AT_TIME ('13-SEP-04 08:10:58');OPEN emp_cur;
DBMS_FLASHBACK.DISABLE;LOOPFETCH emp_cur INTO v_rec;EXITWHEN emp_cur%NOTFOUND;INSERTINTO EMPLOYEE_TEMP VALUES(v_rec.emp_id,
v_rec.age );ENDLOOP;CLOSE emp_cur;COMMIT;END;-- to use the SCN instead, change the ENABLE_AT_TIME line to this:
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.
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.