oracle How to Recover Overwritten Stored Procedures

If you accidentally overwrite the previous stored procedure, you have to flashback quickly. The longer the time, the less likely it is to flashback. The principle is very simple. The definition of stored procedure is data dictionary. There is no difference between modifying data dictionary and modifying data of ordinary table. At this time, the contents before modification will be put into undo. We can flashback according to this point, so I said as soon as possible, otherwise we can't find them back. Let's do an experiment:

1. Create stored procedure at 14:31 under user TEST

 
create or replace procedure GG_TEST 
as l_cnt number; 
begin 
for i in 1 .. 10000 
loop 
execute immediate 'select count(*) from t where x = ' || i into l_cnt; 
end loop; 

end; 

2. Delete stored procedure at 14:33 under user TEST

 
drop procedure GG_TEST; 

3. Log in to the sys account

 
create table p_temp as 
select * 
from dba_source as of timestamp TO_TIMESTAMP('2014-05-04 14:33:00', 'YYYY-MM-DD HH24:MI:SS') 
where TYPE = 'PROCEDURE' 
And owner = 'TEST' 
And Name = 'GG_TEST'; 


select text 
from p_temp 
where name like upper('%GG_TEST%') 
and owner = 'TEST' 

order by line; 

TEXT 
--------------------------------------------------------------------------- 
procedure GG_TEST 
as l_cnt number; 
begin 
for i in 1 .. 10000 
loop 
execute immediate 'select count(*) from t where x = ' || i into l_cnt; 
end loop; 
end;