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

今天有同事给我写信："我大概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 补充：sys@ORCL> select text from dba_source where owner="LSF" and name="EMP_SAL" order by line;TEXT
-----------------------------------------------------------------------------------------------------------------------------------
procedure emp_sal
is
v_last_name employee.last_name%type;
v_employee_id employee.employee_id%type;
v_salary employee.salary%type;
cursor cursor_sal is
select last_name,employee_id,salary from employee where salary between 2000 and 3000;
begin
open cursor_sal;
loop
fetch cursor_sal into v_last_name,v_employee_id,v_salary;
exit when cursor_sal%notfound;
update employee set salary=salary*1.2 where last_name=v_last_name and employee_id=v_employee_id;
end loop;
close cursor_sal;
commit;
end;17 rows selected.
SQL> show user
USER is "LSF"
SQL> select username from user_users;USERNAME
------------------------------
LSFSQL> select text from user_source where name="EMP_SAL" order by line;TEXT
--------------------------------------------------------------------------------
procedure emp_sal
is
v_last_name employee.last_name%type;
v_employee_id employee.employee_id%type;
v_salary employee.salary%type;
cursor cursor_sal is
select last_name,employee_id,salary from employee where salary between 2000 and
3000;begin
open cursor_sal;
loop
fetch cursor_sal into v_last_name,v_employee_id,v_salary;
exit when cursor_sal%notfound;
update employee set salary=salary*1.2 where last_name=v_last_name and employee_i
d=v_employee_id;end loop;
close cursor_sal;
commit;
end;17 rows selected.SQL> select to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） from dual;TO_CHAR（SYSDATE,"YY
-------------------
2011-08-10 14:46:24SQL> drop procedure emp_sal;Procedure dropped.SQL> select text from user_source where name="EMP_SAL" order by line;no rows selectedSQL> select text from user_source as of timestamp to_timestamp（"2011-08-10 14:46:24","YYYY-MM-DD HH24:MI:SS"） where name="EMP_SAL" order by line;
select text from user_source as of timestamp to_timestamp（"2011-08-10 14:46:24","YYYY-MM-DD HH24:MI:SS"） where name="EMP_SAL" order by line
*
ERROR at line 1:
ORA-01031: insufficient privilegessys@ORCL> select text from dba_source as of timestamp to_timestamp（"2011-08-10 14:46:24","YYYY-MM-DD HH24:MI:SS"） where owner="LSF" and name="EMP_SAL" order by line;TEXT
-----------------------------------------------------------
procedure emp_sal
is
v_last_name employee.last_name%type;
v_employee_id employee.employee_id%type;
v_salary employee.salary%type;
cursor cursor_sal is
select last_name,employee_id,salary from employee where salary between 2000 and 3000;
begin
open cursor_sal;
loop
fetch cursor_sal into v_last_name,v_employee_id,v_salary;
exit when cursor_sal%notfound;
update employee set salary=salary*1.2 where last_name=v_last_name and employee_id=v_employee_id;
end loop;
close cursor_sal;
commit;
end;17 rows selected.SUSE Enterprise 10 Linux上Oracle 11g安装笔记Oracle 百万行数据查询优化相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）