• Loading
    • Flashback SQL Queries

      Objectives
      After completing this session, you will be able to:
      • Describe a Flashback Query
      • Describe a Flashback using SCN or Timestamp
      • Describe a Flashback Transaction
      • Describe a Flashback Table
      • Describe a Flashback Row History
      • Describe The Recycle bin


      Introduction

      As part of its read consistency model, Oracle displays data that has been committed to the database. A flashback query is used to see the data as it existed prior to the commit.

      Flashback Query


      As part of its read consistency model, Oracle displays data that has been committed to the database. A flashback query is used to see the data as it existed prior to the commit. You can use the result of the flashback query to restore the result of the data .To support flashback query the database should use system managed undo. System managed undo is a feature introduced in Oracle 9i.To automate management of rollback segments The DBA must create an undo tablespace , enable Automatic undo management , and establish an undo retention time window. To use some features of flashback queries, you must have the EXECUTE privilege on the DBMS_FLASHBACK package

      Time – Based Flashback


      Time - Based Flash back example: Suppose there are 10 rows in product_dim table and while refreshing the table is deleted, but somehow not all the product is included, so the delete is inappropriate.

      Before delete

      Code:
      SELECT COUNT(*) FROM product_dim 
      COUNT(*) 
      ----------- 
      10 
      DELETE FROM product_dim; 
      COMMIT;

      After delete
      Code:
      SELECT COUNT(*) FROM product_dim 
      COUNT(*) 
      ----------- 
      0
      There are two way to restore the data
      1. Data pump import can be used to restore the table.
      2. Perform a physical database recovery to recover the database to a point in time prior to delete.


      However, with flashback queries, you can avoid the need to perform these recovery options.
      Code:
      SELECT COUNT(*) FROM product_dim 
      AS OF TIMESTAMP ( SYSDATE – 5/1440) ; 
      COUNT(*) 
      ----------- 
      10
      Create a back up table to save the data.
      Example:
      Code:
      CREATE TABLE product_dim_old 
      AS SELECT * FROM product_dim 
      AS OF TIMESTAMP ( SYSDATE – 5/1440) ;

      SCN – Based Flashback


      To begin with SCN – based flashback, you must first know the SCN of your transaction. To get the latest change number, issue a commit and then use the AS OF SCN clause of the SELECT COMMAND .You can find the current SCN by executing the GET_SYSTEM_CHANGE_NUMBER function of the DBMS_FLASHBACK package prior to execute your transaction.

      Before delete
      Code:
      SELECT COUNT(*) FROM product_dim 
      COUNT(*) 
      ----------- 
      10 
      DELETE FROM product_dim; 
      COMMIT;
      After delete
      Code:
      SELECT COUNT(*) FROM product_dim 
      COUNT(*) 
      ----------- 
      0 
      SELECT COUNT(*) FROM product_dim 
      AS OF SCN(:SCN_FLASH);
      COUNT(*)
      -----------
      10
      Code:
      CREATE TABLE product_dim_old 
      AS SELECT * FROM product_dim 
      AS OF SCN(:SCN_FLASH);
      SCN_TO_TIMESTAMP can be used to find out the latest timing on which a change is made to a particular table.

      Example:

      To see the SCN associated with each row use ORA_ROWSCN, which is a new feature introduced in Oracle 10g.
      Code:
      INSERT INTO product_dim (product_id, 
      product_name) 
      Values(1009, 
      ’FIXED – 48 Month’) 
      COMMIT; 
      SELECT product_name, ORA_ROWSCN 
      FROM product_dim ; 
      PRODUCT_NAME ORA_ROWSCN 
      ------------------- ---------------------- 
      ARM 553531 
      FIXED 553531 
      FIXED – 48 Month 553853 
      SELECT SCN_TO_TIMESTAMP(553853) 
      FROM dual; 
      SCN_TO_TIMESTAMP(553853) 
      ----------------------------------- 
      20-FEB-04 03.11.28.00000000 PM

      Flashback Table


      The flashback table command restores an earlier state of a table in the event of human or application error. Oracle cannot restore a table to an earlier state across any DDL operations that change the structure of the table .The database should be using Automatic Undo Management (AUM) for flashback to work. The ability to flashback the old data is limited by the amount of undo retained in the undo tablespace and the UNDO_RETENTION initialization parameter setting.
      • You cannot roll back a flashback table statement.
      • Record the current SCN before issuing a flashback table command.
      • You must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.


      Recovering Dropped Tables :
      Code:
      DROP TABLE product_dim;
      As of Oracle 10g, a dropped table does not fully disappear.
      Its blocks are still maintained in its tablespace.
      The dropped objects can be seen by querying RECYCLEBIN data dictionary view.

      Code:
      SELECT * FROM RECYCLEBIN;
      • RECYCLEBIN is a public synonym for the USER_ RECYCLEBIN data dictionary view. DBAs can see all dropped objects by querying DBA_ RECYCLEBIN data dictionary view.
      • The FLASHBACK TABLE TO BEFORE command can be use to recover the table from the RECYCLEBIN.
      • FLASHBACK TABLE product_dim TO BEFORE DROP;


      Flashback table using timestamp or SCN:

      Before flashback the table to the time just prior to a wrong update, first we must enable the row movement for the table.
      Code:
      ALTER TABLE product_dim enable row movement;
      We can then flashback the table.
      Code:
      FLASHBACK TABLE product_dim TO timestamp (systimestamp – 5/1440)
      You can use the TO SCN clause if you wish to specify an SCN instead of a timestamp.
      Code:
      FLASHBACK TABLE product_dim TO SCN (720880)

      Flashback Database


      The flashback database command returns the database to a past time or SCN providing a first alternative to performing incomplete database recovery.

      Following a flashback database operation, in order to have write access to the flashed back database you must reopen it with an ALTER DATABASE OPEN RESETLOGS command.

      You must have the SYSDBA system privilege in order to use the Flashback Database command.
      Syntax:
      Code:
      FLASHBACK TABLE DATABASE TO timestamp (systimestamp – 5/1440)


      Summary
      • As part of its read consistency model, Oracle displays data that has been committed to the database.
      • A flashback query is used to see the data as it existed prior to the commit.
      • To begin with SCN – based flashback, you must first know the SCN of your transaction. To get the latest change number, issue a commit and then use the AS OF SCN clause of the SELECT COMMAND.
      • SCN_TO_TIMESTAMP can be used to find out the latest timing on which a change is made to a particular table.
      • The flashback table command restores an earlier state of a table in the event of human or application error.
      • The flashback database command returns the database to a past time or SCN providing a first alternative to performing incomplete database recovery.


      Test Your Understanding

      • 1. What is time base flashback?
      • 2. What is SCN base flashback?
      • 3. What is the use of ORA_ROWSCN?
      • 4. What is the use of SCN_TO_TIMESTAMP?
      • 5. What is flashback table?
      • 6. What is flashback database?
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.