ORACLE Recover Procedure

1. Connect database as sysdba

2. Write down the sys time

3. Drop one procedure: drop prcedure P_TEST

4. run following script:

select text from dba_source as of timestamp to_timestamp('2014-11-25 17:20:00', 'YYYY-MM-DD HH24:MI:SS') where owner='DB_T' and name= 'P_LOAD_001' order by line;TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE P_LOAD_001(V_EPARCHY_CODE IN VARCHAR2,
                        V_SETT_MONTH   IN VARCHAR2,
                        V_BACK_ID      OUT NUMBER,
                        V_BACK_MSG     OUT VARCHAR2) IS
.....


273 rows selected.

5. copy out the result and recreate the procedure