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 GET_SYSTEM_CHANGE_NUMBER function of the DBMS_FLASHBACK package.

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.

Example 3

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);]

Restrictions

• Some DDLs that alter the structure of a table, such as drop column, invalidate the redo information.. It is not possible to retrieve a snapshot of data from a point earlier than the time such DDLs were executed. Trying such a query results in an ORA-1466 error.

• Oracle tracks the SCNs at five-minute intervals and logs the information for the

  last 5 days of operation. When we enable the Flashback, this log information is used to determine the SCN associated with the time you specify and it is the basis for any flashback queries. Therefore, we are never able to flashback beyond five days and specifying a time will only find Flashback copy to the nearest five-minute interval.

• LOB columns might require significant amounts of storage to keep undo data. Therefore, you must define in advance which LOB columns you want to keep the undo information in, in order to use the undo data for a flashback query.

• Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback.

Conclusions:

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.

Previouis  

Prev  [1] [2] [3]