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

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

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

CREATE OR REPLACE PROCEDURE p_miji AS
BEGIN
  /*这儿是搭讪秘籍一百条*/
  NULL;
END;
/
BEGIN dbms_lock.sleep(2); END;
/
CREATE OR REPLACE PROCEDURE p_miji AS
BEGIN
  /*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 AS
BEGIN
  /*这儿是搭讪秘籍一百条*/
  NULL;
END;

SQL> 

收工搞定。