How to restore the old data using flashback query
My intention is , I want to get back past data of database after erroneously updated and committed.
We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed.
Before proceed ensure that,
•The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query.
•UNDO_MANAGEMENT is set to AUTO.
•In your UNDO TABLESPACE you have enough space.
With an example I will demonstrate the whole procedure.
1)I have created a table named test_flash_table with column name and salary.
SQL> create table test_flash_table(name varchar2(10), salary number);
Table created.
SQL> insert into test_flash_table values('ABCD',10);
1 row created.
SQL> commit;
Commit complete.
The table contains one row.
2)I erroneously updated column salary of Arju and commited data.
SQL> update test_flash_table set salary=20 where name='ABCD';
1 row updated.
SQL> commit;
Commit complete.
3)After some moments I found that I have made wrong update. Now be sure to query. Also select that time SCN by TIMESTAMP_TO_SCN.
SQL> select name, salary,systimestamp, TIMESTAMP_TO_SCN(SYSTIMESTAMP-interval '8' minute) SCN from test_flash_table as of timestamp (SYSTIMESTAMP-interval '8' Minute);
NAME SALARY SYSTIMESTAMP SCN
---------- ---------- ---------------------------------------- ----------
ABCD 10 29-APR-12 12.34.03.452330 AM -04:00 869222
4)Now update the data based on the SCN.
SQL> update test_flash_table set salary=(select salary from test_flash_table as of scn 869222 where name='ABCD') where name='ABCD';
1 row updated.
SQL> select * from test_flash_table where name='Arju';
NAME SALARY
---------- ----------
ABCD 10
