How Does oracle restore a covered stored procedure?

If you accidentally overwrite the previous stored procedure, You have to flash back quickly. The longer the duration, the less likely it will be to flash back. The principle is very simple. The definition of a stored procedure is a data dictionary. modifying a data dictionary is no different from modifying the data in a common table. In this case, the pre-modification content will be placed in undo, we can flash back based on this point, so we should try to do it as soon as possible, or we will not be able to find it. Here is an experiment:

1. Create a stored procedure at under user TEST
Copy codeThe Code is as follows:
Create or replace procedure GG_TEST
As l_cnt number;
Begin
For I in 10000
Loop
Execute immediate 'select count (*) from t where x = '| I into l_cnt;
End loop;

End;

2. Delete the stored procedure under user TEST
Copy codeThe Code is as follows:
Drop procedure GG_TEST;

3. log on to the sys account
Copy codeThe Code is as follows:
Create table p_temp
Select *
From dba_source as of timestamp TO_TIMESTAMP ('2017-05-04 14:33:00 ', 'yyyy-MM-DD HH24: MI: ss ')
Where TYPE = 'processed'
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 10000
Loop
Execute immediate 'select count (*) from t where x = '| I into l_cnt;
End loop;
End;