如何恢复一个被误drop的存储过程

原文地址：http://dbsnake.com/2009/03/recover-dropped-procedure.html

 

今天有同事给我写信："我大概 10 分钟前错误地 drop 掉了一个存储过程： P_IPACCHECK_NC ，而这个存储过程的源码我本机又没有备份，麻烦您恢复一下，谢谢"

 

这种恢复是非常容易的，原理就是利用了 oracle 里所有的存储过程的源代码都是存在 dba_source 里，而 drop 某个存储过程的时候， oracle 这里肯定要去 dba_source 里把相关的源代码给 delete 掉，既然是 delete ，那就好办咯，直接 flashback query 就可以了 。

 

如下是完整的恢复过程：

用 sys 用户登陆，执行如下的查询：

SQL> select text from dba_source as of timestamp to_timestamp('2009-03-06 09:45:00', 'YYYY-MM-DD HH24:MI:SS') where owner='IPRA' and name= 'P_IPACCHECK_NC' order by line;

 

TEXT

--------------------------------------------------------------------------------

procedure P_IPACCHECK_NC(n_flag     out number,

                                           vc_message out varchar2) is

  ------------------------------------------------------------------------------

  --    PROCEDURE NAME      : P_IPACCHECK_NC                       --

  --    NAME IN SYSMTH      : NONE                                --

  --    DESCRIPTION         : 对 IWBIBT 记录进行有效性检查，没有错误的数据置标志为

  --

  --       INVOKED          :                                     --

  --    PROGRAMMED BY       : ZhouXin         DATE  2008/12/02    --

  --     MODIFIED BY        :

  --         TYPE           : ONLINE                               --

  --                   COPYRIGHT 1997~2008 ACCA-ARK               --

  --                                                              --

  ------------------------------------------------------------------------------

  vc_ipastc  varchar2(20);

  n_errcount number := 0;

begin

  for rec_pac in (select * from iwbpac where ipastc is null) loop

 

TEXT

--------------------------------------------------------------------------------

    n_errcount := 0;

    vc_ipastc  := rec_pac.ipastc;

    -- 检查清算月

    if rec_pac.ipalrm > to_number(to_char(sysdate, 'YYYYMM')) then

      vc_ipastc  := vc_ipastc || 'A';

      n_errcount := n_errcount + 1;

    end if;

    -- 检查名义开账公司

    if f_masaln_existawbprefix(rec_pac.ipaarr) != true then

      vc_ipastc  := vc_ipastc || 'B';

      n_errcount := n_errcount + 1;

    end if;

    -- 检查实际开账公司

    if f_masaln_existawbprefix(rec_pac.ipacar) != true then

      vc_ipastc  := vc_ipastc || 'C';

      n_errcount := n_errcount + 1;

    end if;

    -- 检查开账公司

    if f_masaln_existawbprefix(rec_pac.ipairl) != true then

      vc_ipastc  := vc_ipastc || 'E';

      n_errcount := n_errcount + 1;

 

TEXT

--------------------------------------------------------------------------------

    end if;

    -- 检查名义开账公司

    if rec_pac.ipalas <> 'P' then

      vc_ipastc  := vc_ipastc || 'F';

      n_errcount := n_errcount + 1;

    end if;

    -- 检查帐单录入日期

    if rec_pac.ipanpd > to_number(to_char(sysdate, 'YYYYMMDD')) then

      vc_ipastc  := vc_ipastc || 'G';

      n_errcount := n_errcount + 1;

    end if;

    -- 检查开账月

    if rec_pac.ipailm > to_number(to_char(sysdate, 'YYYYMM')) then

      vc_ipastc  := vc_ipastc || 'H';

      n_errcount := n_errcount + 1;

    end if;

    -- 检查原始开账金额

    if rec_pac.ipaemk = 'B' and rec_pac.ipaamt is null then

      vc_ipastc  := vc_ipastc || 'I';

      n_errcount := n_errcount + 1;

    end if;

 

TEXT

--------------------------------------------------------------------------------

    -- 检查清算期

    if to_number(rec_pac.ipacpr) < 1 or to_number(rec_pac.ipacpr) > 4 then

       vc_ipastc  := vc_ipastc || 'J';

      n_errcount := n_errcount + 1;

    end if;

    -- 检查开账期

    if to_number(rec_pac.ipabpr) < 1 or to_number(rec_pac.ipabpr) > 4 then

      vc_ipastc  := vc_ipastc || 'K';

      n_errcount := n_errcount + 1;

    end if;

     -- 没有错误，置标志位 '0'

    if n_errcount = 0 then

      update iwbpac

         set ipastc = '0'

       where ipacpr = rec_pac.ipacpr

         and ipairl = rec_pac.ipairl

         and ipacar = rec_pac.ipacar

         and ipanvn = rec_pac.ipanvn

         and ipanva = rec_pac.ipanva

         and ipalrm = rec_pac.ipalrm;

    else

 

TEXT

--------------------------------------------------------------------------------

      update iwbpac

         set ipastc = vc_ipastc

       where ipacpr = rec_pac.ipacpr

         and ipairl = rec_pac.ipairl

         and ipacar = rec_pac.ipacar

         and ipanvn = rec_pac.ipanvn

         and ipanva = rec_pac.ipanva

         and ipalrm = rec_pac.ipalrm;

    end if;

  end loop;

exception

  when others then

    n_flag     := 0;

    vc_message := substr(sqlerrm, 1, 1000);

end P_IPACCHECK_NC;

 

100 rows selected

 

呵呵，剩下该怎样做就不用我说了吧。