oracle 恢复被覆盖的存储过程

来源：互联网发布：如何劫持域名编辑：程序博客网时间：2024/04/28 19:51

--很多时候，存储过程一不小心就被覆盖掉了。很焦急的我们，无手可入，不知道如何恢复，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>

0 0