如何恢复一个被误drop的存储过程
如何恢复一个被误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
------------------------------
LSF
SQL> 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:24
SQL> drop procedure emp_sal;
Procedure dropped.
SQL> select text from user_source where name='EMP_SAL' 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 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 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 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.
评论暂时关闭