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