Query Flashbacks

If you mistakenly commit changes and you want to view rows as they originally were, you can use a query flashback. You can then use the results of a query flashback to manually change rows back to their original values if you need to.

In addition, flashbacks can be based on a datetime or system change number (SCN). The database uses SCNs to track changes made to data, and you can use them to flash back to a particular SCN in the database.

Granting the Privilege for Using Flashbacks

Flashbacks use the PL/SQL DBMS_FLASHBACK package, for which you must have the EXECUTE privilege. The following example connects as the sys user and grants the EXECUTE privilege on DBMS_FLASHBACK to the store user:

 CONNECT sys/change_on_install AS sysdba GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO store; 

Note  

Speak with your DBA if you get errors when performing these statements. You ll learn more about privileges in the next chapter. You ll learn more about PL/SQL packages in Chapter 11.

Time Query Flashbacks

The following example connects as store and retrieves the product_id , name , and price columns for the first five rows from the products table:

  CONNECT store/store_password   SELECT product_id, name, price   FROM products   WHERE product_id  <  = 5;  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  1 Modern Science 19.95  2 Chemistry 30  3 Supernova 25.99  4 Tank War 13.95  5 Z Files 49.99 

The next example reduces the price of these rows, commits the change, and retrieves the rows again so you can see the new prices:

  UPDATE products   SET price = price * 0.75   WHERE product_id  <  = 5;   COMMIT;   SELECT product_id, name, price   FROM products   WHERE product_id  <  = 5;  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  1 Modern Science 14.96  2 Chemistry 22.5  3 Supernova 19.49  4 Tank War 10.46  5 Z Files 37.49 

The next example executes the DBMS_FLASHBACK.ENABLE_AT_TIME() procedure, which enables you to perform a flashback to a particular datetime. This procedure accepts a datetime, and the example passes SYSDATE “ 10 / 1440 to the procedure, which is ten minutes in the past:

 EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE  10 / 1440); 

Note  

24 hours * 60 minutes per hour = 1440 minutes.

Any queries you execute now will display the rows as they were ten minutes ago. Assuming you performed the earlier UPDATE less than ten minutes ago, the following query will display the prices as they were before you updated them.

  SELECT product_id, name, price   FROM products   WHERE product_id  <  = 5;  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  1 Modern Science 19.95  2 Chemistry 30  3 Supernova 25.99  4 Tank War 13.95  5 Z Files 49.99 

To disable a flashback, you execute DBMS_FLASHBACK.DISABLE() , as shown in the following example:

 EXECUTE DBMS_FLASHBACK.DISABLE(); 

Caution  

You must disable a flashback before you can enable it again.

Now when you perform queries, the rows as they currently exist will be retrieved. For example:

  SELECT product_id, name, price   FROM products   WHERE product_id  <  = 5;  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  1 Modern Science 14.96  2 Chemistry 22.5  3 Supernova 19.49  4 Tank War 10.46  5 Z Files 37.49 

System Change Number Query Flashbacks

Flashbacks based on system change numbers (SCNs) can be more precise than those based on a time, because the database uses SCNs to track changes. To get the current SCN, you can execute DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() . For example:

  VARIABLE current_scn NUMBER   EXECUTE :current_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();   PRINT current_scn  CURRENT_SCN -----------  292111 

The next example adds a row to the products table, commits the change, and retrieves the new row:

  INSERT INTO products (   product_id, product_type_id, name, description, price   ) VALUES (   15, 1, 'Physics', 'Textbook on physics', 39.95   );   COMMIT;   SELECT *   FROM products   WHERE product_id = 15;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- ------------------------------ DESCRIPTION PRICE -------------------------------------------------- ----------  15 1 Physics Textbook on physics 39.95 

The next example executes the DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER( ) procedure, which enables you to perform a flashback to an SCN. This procedure accepts an SCN and the example passes the current_scn variable to the procedure:

 EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn); 

Any queries you execute now will display the rows as they were at the SCN stored in current _scn before you performed the INSERT . The following query attempts to get the row with a product_id of 15 and fails because that new row was added after the SCN stored in current_scn :

  SELECT product_id   FROM products   WHERE product_id = 15;  no rows selected 

To disable a flashback, you execute DBMS_FLASHBACK.DISABLE() , as shown in the following example:

 EXECUTE DBMS_FLASHBACK.DISABLE(); 

If you perform the previous query again, you ll see the new row that was added by the INSERT .

Note  

If you followed along with the examples, go ahead and rerun the store_schema.sql script to re-create everything. That way, the results of your SQL statements will match mine as you progress through the rest of this book.