这种恢复是非常容易的，原理就是利用了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 来2源gaodaima#com搞(代@码&网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的存储过程
