Recover the table data back in time with Flashback Query

Recover the table data back in time with Flashback Query

Flashback is a pretty useful feature in the Oracle 9i. It can save a lot of time and energy. To use this feature the UNDO_MANAGEMENT should be set to AUTO in the database. And offcourse the UNDO_RETENTION should be large enough so the Flashback Query can recove the data back in time.

We recovered one of the table data. Some records were updated incorrectly and was commited. Then we recovered the old data just before the update statement took place. we used the following procedure and we followed the metlaink document number 174425.1. We went 60 minutes back in time from the current time.

Procedure to recover data back in time with Flashback feature of Oracle 9i:

DECLARE
CURSOR FLASH_RECOVER IS
select * from LIST_GENERATION;
list_generation_recover_rec LIST_GENERATION%ROWTYPE;
begin
DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate - 60/1440);
open FLASH_RECOVER;
DBMS_FLASHBACK.DISABLE;
loop
FETCH FLASH_RECOVER INTO list_generation_recover_rec;
EXIT WHEN FLASH_RECOVER%NOTFOUND;
insert into LIST_GENERATION
values
(list_generation_recover_rec.PARTY_ID ,
list_generation_recover_rec.TRANS_DATE ,
list_generation_recover_rec.CALL_FLAG ,
list_generation_recover_rec.CAMPAIGN_SCHEDULE_ID ,
list_generation_recover_rec.LIST_TYPE ,
list_generation_recover_rec.REQUEST_ID ,
list_generation_recover_rec.CREATION_DATE ,
list_generation_recover_rec.CREATED_BY ,
list_generation_recover_rec.LAST_UPDATE_DATE ,
list_generation_recover_rec.LAST_UPDATED_BY );
end loop;
CLOSE FLASH_RECOVER;
commit;
end;

PL/SQL procedure successfully completed.

apps-sql>select count(*) from LIST_GENERATION;

COUNT(*)
----------
5693