Oracle 如何修复被修改的存储过程？

背景

 开发人员较多，一般都是共同维护和开发数据库中的存储过程，可能会导致存储过程被人修改，或者被误删除。有的时候，我们需要重新恢复存储过程内容。

那么该如何恢复呢？
 

恢复存储过程

通过下面实验，简单看一下：

1. 创建测试存储过程

--1) 创建测试表
06:50:41 SQL> create table user_info (id number(10) primary key,name varchar2(30));

Table created.

06:51:21 SQL> create table class_info(id number(10) primary key,class varchar2(30));

Table created.

--2) 创建存储过程
06:51:41 SQL> create or replace procedure test_proc(id IN NUMBER, name IN VARCHAR2) is
06:51:59   2  begin
06:52:02   3  insert into user_info values (id, name);
06:52:06   4  commit;
06:52:10   5  end;
06:52:14   6  /

Procedure created.

--3) 执行存储过程
06:54:44 SQL> begin
06:54:48   2  test_proc(1,'leo');
06:54:51   3  dbms_output.put_line('record inserted successfully');
06:54:55   4  end;
06:55:02   5  /

PL/SQL procedure successfully completed.

-- 数据插入成功
06:55:41 SQL> select * from user_info;

        ID NAME
---------- ------------------------------
         1 leo

2. 修改存储过程

-- 将存储过程的user_info 更换为class_info表
07:01:25 SQL> create or replace procedure test_proc(id IN NUMBER, name IN VARCHAR2) is
07:01:29   2  begin
07:01:31   3  insert into class_info values (id, name);
07:01:35   4  commit;
07:01:37   5  end;
07:01:41   6  /

Procedure created.

我们记录一下时间：

• 06:51:41 创建了procedure，操作user_info表。

• 07:01:25 修改了procedure，将存储过程的user_info 更换为class_info表。

3. 查看存储过程之前的内容

方法1： 闪回查询dba_source

我们通过闪回查询，查询该存储过程修改前（07:01:25之前）存储过程的内容。

SQL> select * from dba_source as of timestamp TO_TIMESTAMP('2023/5/19 06:52:00','YYYY-MM-DD HH24:MI:SS') where name='TEST_PROC' and owner='SXC' order by line;

结论：通过闪回查询，可以看到该存储过程内容操作的是user_info 表，说明是修改之前的。 

方法2： 闪回查询source$

--1) 查询存储过程的OBJ#
--使用sys用户
SQL> conn / as sysdba  
Connected.
SQL> select obj# from source$ as of timestamp  TO_TIMESTAMP('2023/5/19 06:52:00','YYYY-MM-DD HH24:MI:SS')  where source like '%test_proc%';

  OBJ#
----------
  96245

--注意：这里source like '%test_proc%'是区分大小写的，也就是说，原来存储过程中是小写的，这里也要小写，如果不确定，可以都强制大写 ，改为upper(source) like '%TEST_PROC%'。

--2) 根据OBJ#查询具体的存储过程内容
SQL> select * from source$ as of timestamp  TO_TIMESTAMP('2023/5/19 06:52:00','YYYY-MM-DD HH24:MI:SS')  where obj#=96245 order by line;

结论：同样可以看到，该存储过程内容操作的是user_info 表，说明也是修改之前的。

4. 删除存储过程

– 通过drop直接删除存储过程，闪回查询还可行吗？

07:23:56 SQL> drop procedure test_proc;

Procedure dropped.

通过闪回查询：

-- 方法1：
SQL> select text from dba_source as of timestamp TO_TIMESTAMP('2023/5/19 07:02:00','YYYY-MM-DD HH24:MI:SS') where name='TEST_PROC' and owner='SXC' order by line;

TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------
procedure test_proc(id IN NUMBER, name IN VARCHAR2) is
begin
insert into class_info values (id, name);
commit;
end;

-- 方法2：
SQL> select source from source$ as of timestamp  TO_TIMESTAMP('2023/5/19 06:55:00','YYYY-MM-DD HH24:MI:SS')  where obj#=96245 order by line;

SOURCE
-------------------------------------------------------------------------------------------------------------------------------------------------
procedure test_proc(id IN NUMBER, name IN VARCHAR2) is
begin
insert into user_info values (id, name);
commit;
end;

结论： 即使使用drop命令删除存储过程，也可以用过闪回查询，找回存储过程内容，且方法1、方法2 都可以查找到最开始创建和修改之后的存储过程内容。

5. 其他问题

1. 权限问题

– 普通用户查询dba_source视图没有权限，需要重新赋予权限

SQL> conn sxc/sxc
Connected.
SQL> select * from dba_source as of timestamp TO_TIMESTAMP('2023/5/19 06:52:00','YYYY-MM-DD HH24:MI:SS') where name='TEST_PROC' and owner='SXC';
select * from dba_source as of timestamp TO_TIMESTAMP('2023/5/19 06:52:00','YYYY-MM-DD HH24:MI:SS') where name='TEST_PROC' and owner='SXC'
              *
ERROR at line 1:
ORA-01031: insufficient privileges

– 解决办法：赋予如下权限

GRANT SELECT , FLASHBACK ON DBA_SOURCE TO SXC; 

测试中，只需要给FLASHBACK 权限，就可以访问DBA_SOURCE视图。

2. 快照过旧

 和普通闪回查询一样，当undo 表空间信息被覆盖后，闪回查询同样抛出ORA-01555错误。可以通过数据泵定期备份存储过程。

SQL> select source from source$ as of timestamp  TO_TIMESTAMP('2023/5/19 06:55:00','YYYY-MM-DD HH24:MI:SS')  where obj#=96245 order by line;
select source from source$ as of timestamp  TO_TIMESTAMP('2023/5/19 06:55:00','YYYY-MM-DD HH24:MI:SS')  where obj#=96245 order by line
                   *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_2070203016$" too small