欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-17 20:49:47
...

这种恢复是非常容易的,原理就是利用了oracle里所有的存储过程的源代码都是存在dba_source里,而drop某个存储过程的时候,oracle

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

如下是完整的恢复过程:

用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) 4 then

vc_ipastc := vc_ipastc || 'J';

n_errcount := n_errcount + 1;

end if;

--检查开账期

if 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 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
------------------------------
LSF

SQL> select text from user_source where 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:24

SQL> drop procedure emp_sal;

Procedure dropped.

SQL> select text from user_source where order by line;

no rows selected

SQL> select text from user_source as of timestamp to_timestamp('2011-08-10 14:46:24','YYYY-MM-DD HH24:MI:SS') where 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 order by line
*
ERROR at line 1:
ORA-01031: insufficient privileges

sys@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 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.

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