Flash Back Table

This article will help you recover a table upto a certain time. This Procedure was tested on Oracle 10g Release 2 Firstly create a test table which will be used for testing

Create table Test_tmp as select * from emp where 1=2;

Insert some values and issue a commit.
Now select the systimestamp from the database.

Select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-FEB-07 03.10.47.906882 PM +05:00

Now delete data from the Test_tmp table and commit it.

Delete from test;
Commit;

Now select the systimestamp again from the database.
select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-FEB-07 03.15.47.906882 PM +05:00

Now you know that table was truncated at 3:15. So we will perform recovery / Flashback till 3:11

INSERT INTO TEST_TMP
(SELECT * FROM TEST AS OF TIMESTAMP TO_TIMESTAMP(\'09-FEB-07 03:11:00\',\'DD-MON-YY HH24: MI: SS\'))

Commit;

Now you can see the data has been restored in the table.

SQL> Select * from TEST_TMP;