oracle 恢复被覆盖的存储过程

--很多时候，存储过程一不小心就被覆盖掉了。很焦急的我们，无手可入，不知道如何恢复，google，百度，问同事啊，都没有办法解决。
这里介绍一下恢复的过程，2个步骤就搞定。

 

--登录到sys用户下，创建临时表（600表示600秒之前的，最后一个存储过程）

SQL> create table ML_TEST_PROTEMP
  2  as
  3  select * from all_source  as of timestamp (systimestamp -interval'600'second)
  4  where TYPE = 'PROCEDURE' And owner = 'MOSS_V4'
  5  And Name = 'SP_DD' ;
 
Table created

 

--查看被覆盖的存储过程

SQL> select text from ML_TEST_PROTEMP  
  2  where  name like upper('%sp_dd%') and owner ='MOSS_V4'  
  3  order by line;    
TEXT  
--------------------------------------------------------------------------------  
PROCEDURE sp_dd(  
                       i_username1       VARCHAR2,  
                       i_top_username    VARCHAR2 DEFAULT NULL  
                      )  
AS  
BEGIN  
    EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema='||i_top_username;  
    FOR v_cur IN (SELECT * from all_tables WHERE owner=i_top_username  )  
    LOOP  
        BEGIN  
        EXECUTE IMMEDIATE 'GRANT ALL ON ' || i_top_username||'.'||v_cur.table_na  
        EXCEPTION  
        WHEN OTHERS THEN  
          k_log.sp_error('赋权异常:'||v_cur.table_name);  
          RAISE;  
          END;  
    END LOOP;  
    k_log.sp_error('赋权成功');  
   
TEXT  
--------------------------------------------------------------------------------  
    EXCEPTION  
      WHEN OTHERS THEN  
        k_log.sp_error('赋权异常');  
        RAISE;  
END sp_dd;  
   
25 rows selected  
   
SQL>