Flashback Query for Restoring the Table Entries from UNDO

If you wrongly delete any table entry from oracle database level and you want to get your data back then you can use this procedure easily…

For an example we are going to delete some entries in a SAP table (TADIR)

- Check the related entries from TADIR table

SQL> select count(*) from sapsr3.tadir where TABNAME like ‘/test%’;

COUNT(*)
———-
29

- Delete the related entries from TADIR table

SQL> DELETE sapsr3.tadir where  TABNAME like ‘/test%’;

29 rows deleted.

SQL> commit ;

- Let’s check it again

SQL> select count(*) from sapsr3.tadir where TABNAME like ‘/test%’;

COUNT(*)
———-
 0

- But you realize that those entries are needed by system or someone else  :( don’t worry and try this one

SQL> insert into sapsr3.tadir select * from sapsr3.tadir AS OF TIMESTAMP
TO_TIMESTAMP(’2014-07-10 10:00:00′, ‘YYYY-MM-DD HH:MI:SS’)
where devclass like ‘/test%’;

29 rows created.

SQL> commit;

- Now let’s check the records

SQL> select count(*) from sapsr3.tadir where TABNAME like ‘/test%’;

COUNT(*)
———-
29

All records restored from the UNDO and please don’t forget that it depends to your UNDO_RETENTION parameter value….

 

 

Leave a Reply


eight − 3 =

Blogroll