How to Recover Dropped PACKAGE, PROCEDURE OR FUNCTION.

This might happen to anyone who are developing a code, there might be chances that you accidentally dropped your code or accidentally overwritten the code.

Oracle has provided various flashback features through which you can recover your code.

Now let us see how to recover your code.

I have created a sample function as shown below.

CREATE OR REPLACE FUNCTION APPS.FLASHTEST

RETURN DATE

IS

result DATE;

BEGIN

result:=NULL;

SELECT SYSDATE INTO result FROM DUAL;

RETURN result;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END FLASHTEST;

/

Now let us check the system time.

Now I will update this function by adding some output lines.

CREATE OR REPLACE FUNCTION APPS.FLASHTEST

RETURN DATE

IS

result DATE;

BEGIN

result:=NULL;

SELECT SYSDATE INTO result FROM DUAL;

dbms_output.put_line('Added Extra line '||result);

RETURN result;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END FLASHTEST;

/

Now let us recollect the old version of this function, we can use the below script to recollect the old version.

SELECT text

FROM all_source

AS OF TIMESTAMP TO_TIMESTAMP('16-dec-2016 03:52:40','DD-MON-YYYY HH24:MI:SS')

WHERE name='FLASHTEST' AND TYPE='FUNCTION';

In the same way you can recover your code even if you dropped it; one thing need to remember is that this data is only available as long as the UNDO data is not overwritten.

We can check the undo by using the below script.

SELECT a.bytes UNDO_SIZE

,c.tablespace_name

,c.retention

FROM v$datafile a,

v$tablespace b,

dba_tablespaces c

WHERE c.contents='UNDO'

AND c.status='ONLINE'

AND b.name=c.tablespace_name

AND a.ts# =b.ts#;