Recovering Accidentally Lost Data Using Oracle's Flashback Query - Page 3
May 9, 2003by Sreeram Surapaneni
Example 2 Using "AS OF " clause with SCN:
A precise method of specifying the flashback point is using the SCN directly, instead of specifying a timestamp as in the above example, but it requires that we store the SCN in advance. The current system change number can be returned using the
Let us take another example where an application developer needs to build new functionality using the past versions of user data and application level, end user service-level error correction capabilities. In the example below, records in the users table, where users belong to state 'CA' and 'NY,' were deleted by the application interface [ See procedure line DELETE FROM users WHERE state in ('CA,'NY');]. We can use the data that existed immediately before the delete by inserting it into the users table, [see OPEN c FOR 'SELECT * FROM users AS OF SCN :1 WHERE state in ('CA','NY'); below.]
DECLARE TYPE user_cur IS REF CURSOR; c user_cur; cvar users%ROWTYPE; old_scn NUMBER; BEGIN COMMIT; dbms_flashback.disable; old_scn := dbms_flashback.get_system_change_number; DELETE FROM users WHERE state in ('CA,'NY'); /* User choose to delete California and New York users from users table */ COMMIT; /* Find that earlier delete was in error. Need to use the data it existed immediately before delete. */ OPEN c FOR 'SELECT * FROM users AS OF SCN :1 WHERE state in ('CA','NY'); USING old_scn; LOOP FETCH c INTO cvar; EXIT WHEN c%NOTFOUND; dbms_output.put_line ('Recovering 'CA', and 'NY' users: ' || cvar.last_name); INSERT INTO users VALUES cvar; END LOOP; COMMIT; END; /
NOTE: It is important we note that SYSDATE and DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER functions always return the current SYSDATE and SCN respectively, regardless of whether you are in flashback query mode or not.
To find all users that were added today to the users table, or to find the users whose data was changed today so their data doesn't match the data from yesterday is illustrated with following query.
CREATE TABLE users_changed_today AS SELECT * FROM users MINUS SELECT * FROM users AS OF TIMESTAMP TRUNC (SYSDATE);]
The Flashback Query feature enables recovery of an entirely new class of errors--those committed by users, with little or no intervention from dba. It provides an online mechanism to recover from such errors, thereby enhancing system availability and business profitability. However, the Flashback query mode can be entered only at the beginning of a transaction. If you have issued DML statements to change your data, then you must COMMIT before you can flash back. In addition, you will not be able to flash back to a point in time prior to the most recent change in a table's structure. That is because flashback queries use current data dictionary data. Even a simple change to a column datatype limits your ability to flash back.