用闪回功能恢复被更改的存储过程

来源：互联网发布：两个excel相同数据筛选编辑：程序博客网时间：2024/06/04 20:08

关于闪回的介绍见前面复习闪回功能

下面是我们在用户test里建的proc及更改

CREATE OR REPLACE PROCEDURE p_miji ASBEGIN  /*这儿是搭讪秘籍一百条*/  NULL;END;/BEGIN dbms_lock.sleep(2); END;/CREATE OR REPLACE PROCEDURE p_miji ASBEGIN  /*xxxxx*/  /*20131212关键字屏蔽，知名不具*/  NULL;END;/COMMIT/

这数据被改掉了，这可不得了，一定要找回来，希望时间不算太晚，于是用sys用户做查询如下。

SELECT versions_starttime,       versions_startscn,       versions_endtime,       versions_endscn,       s.line,       s.source  FROM sys.source$ versions BETWEEN TIMESTAMP minvalue AND maxvalue s WHERE s.obj# = (SELECT o.obj#                   FROM sys."_CURRENT_EDITION_OBJ" o, sys.user$ u                  WHERE o.owner# = u.user#                    AND (o.type# IN (7, 8, 9, 11, 12, 14, 22) OR                        (o.type# = 13 AND o.subname IS NULL))                    AND u.name = upper('test') /*owner*/                    AND o.name = upper('p_miji') /*object_name*/                 ) ORDER BY 2 DESC NULLS LAST;

ps:图片显示不全的，自个儿复制出来看

看来还有救，按所查信息直接取得对应的source即可。

SQL> SELECT text  2    FROM dba_source as of scn 9460948  3   WHERE NAME = upper('p_miji')  4     AND owner = upper('test');TEXT--------------------------------------------------------------------------------PROCEDURE p_miji ASBEGIN  /*这儿是搭讪秘籍一百条*/  NULL;END;SQL>

收工搞定。

0 0