How Oracle restores overwritten stored procedures

If you accidentally overwrite the previous stored procedure, that must quickly flash back, the longer the likelihood of flash back less, the following for you to introduce the principle of recovery

If you accidentally overwrite the previous stored procedure, then flash back and the likelihood of a long, long flashback is smaller. The principle is very simple, the definition of stored procedures is a data dictionary, modify the data dictionary and modify the normal table data no difference, at this time will be the contents of the change before the undo, we can according to this point to flash back, so I said to be as soon as possible, or can not find back. Now let's do an experiment:

1. Create a stored procedure under user test under 14:31

The code is as follows:

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 under user test at 14:33

The code is as follows:

drop procedure Gg_test;

3. Log in to the SYS account

Copy code code as follows:

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