|
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; |
| 
