Flashback queries in oracle

[Laquan]

I am using sql*plus and I want to use and see only selected data that was created and entered at appropriate time. Can you tell me any method to access in this situation .I have deleted a table accidentally and need to get it back Please would you help me recover it again,Its very important data table for me.Your help would be appreciated.

Thanks and regards
Laquan

[Praetor]

Flashback Query in Oracle:

The oracle flashback query is used to view the data that was entered at a point of time. It allows you recover your lost object that you have made earlier. It provides very limited operation to perform.

To use the flashback query,you need to have privilege to execute it and the Automatic undo management should be configured on the database server.

[Katty]

Enabling flashback query :

The DBMS_FLASHBACK package which is used to enable and disable the flashback query at anytime,you can use the SCN(System Change Number) or exact time to enter the recovery.

EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123);
EXECUTE Dbms_Flashback.Enable_At_Time('28-AUG-01 11:00:00');

You can turn off the flashback query after all operations have performed regarding flashback queries using below :

EXECUTE Dbms_Flashback.Disable;

[kelfro]

Example of flashback queries :

You can use this example to test the working of flashback queries.you need to connect first as a sysdba user of database and grant a user to execute the flashback query
-------Using sysdba schema----------

SQL>GRANT EXECUTE ON dbms_flashback TO Jackson;

now login as Jackson/jack and Create a table :

SQL> CREATE TABLE order_entry (order_no NUMBER(5),
order_date DATE, order_histrory VARCHAR2(100));

Insert data into table :
SQL> INSERT INTO.........
SQL> INSERT INTO.........
SQL> INSERT INTO.........

SQL> COMMIT;
And execute the SELECT statement......

SQL>select * from order_entry;

Three rows would be selected and now insert one more row :

SQL> INSERT INTO..........

SQL>COMMIT;

Now if you want to select only first three rows from table then you can select using flashback query by :

SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('<Time of lat row entered>','DD-MON-YYYY:HH24MISS'));

[Zecho]

Recovery of deleted table contents

Suppose the data of mine table has been deleted and need to recover it so you have to create a CURSOR which will help you to recover it again. The contents of table is deleted at 11:00 PM.

DECLARE
  CURSOR Cur_mine IS
    SELECT *
    FROM   Mine;
  v_row Cur_mine%ROWTYPE;
BEGIN
  Dbms_Flashback.Enable_At_Time('04-JAN-10 10:55:00');
  OPEN Cur_Mine;
  Dbms_Flashback.Disable;

  LOOP 
    FETCH Cur_Mine INTO v_row; 
    EXIT WHEN Cur_Mine%NOTFOUND; 
    INSERT INTO Mine VALUES
    (v_row.Member_id, v_row.first_name, 
     v_row.last_name, v_row.email_address, 
     v_row.Contact_number, v_row.hire_date,
     ); 
  END LOOP; 
  CLOSE Cur_Mine;
  COMMIT;
END;