Flashbacks Time Base Recovery

http://www.conacent.com

Accidentally, the user executes a wrong query, probably with & without a WHERE clause, and COMMITS the data. Finally, when he queries the table to check the record count, he notices that all or some of the rows from the table are deleted. The Administrator uses the Flashback Query feature to restore the data to prior point of time, the data as existed some time minutes back.


1.  Suppose we have created one table named test
    SQL> create table test (name varchar2(10));
 



2. Insert few records into the table
    SQL> insert into test (name) values ('aa');



3. Check the date and time of the system
    SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

4. Delete all records or some of it.
    SQL> delete from test;
or
    SQL> delete from test where name='cc';






5. Commit after you have deleted the record
    SQL> commit;

6. Check the date and time of the system
    SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

7. Place the value  of <Date & Time> taken from step 3.
    If all the records are deleted then use the below query
    SQL> insert into test select * from test as of timestamp to_timestamp('<Date & Time>','dd-mm-yyyy hh24:mi:ss');

   If specific record is deleted then use the below query
   SQL> insert into test select * from test as of timestamp to_timestamp('<Date & Time>','dd-mm-yyyy hh24:mi:ss') where name='cc' ;