哪些操作易引起存储过程失效？

dba_dependencies视图保存了对象之间的依赖关系，简单的说如果存储过程P1里会去访问视图V1那么对于V1的修改可能会导致P1失效，即P1的有效性依赖于V1，在这里P1称作dependent object、V1称作referenced object。虽然对象失效后的首次执行会自动进行重编译，但如果失效的对象存在高并发的访问，就会出现大量library cache lock/pin等待事件，严重时会使数据库性能急剧下降直至停止响应。我们通过以下的测试案例，看看那些容易被我们忽略的会引起对象失效的操作

//
/// pl/sql object中新增条目必须要加在最后，才不会使得dependent object的状态变为invalid
//
---创建测试用package、procedure
create or replace package pkg1 is
function f1 return varchar2;
function f2 return varchar2;
procedure p1(v1 varchar2);
end;
/


create or replace procedure top_p is
begin
pkg1.p1('A');
end;
/


col name format a15
col referenced_name format a30
col owner format a15
col type format a15
set linesize 150
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='TOP_P';


OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
AD              TOP_P           PROCEDURE       AD                             PKG1                           PACKAGE            VALID
AD              TOP_P           PROCEDURE       SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID
AD              TOP_P           PROCEDURE       SYS                            STANDARD                       PACKAGE            VALID


---调整pkg1加入一行procedure p2，加在中间
create or replace package pkg1 is
function f1 return varchar2;
function f2 return varchar2;
procedure p2(v2 varchar2);
procedure p1(v1 varchar2);
end;
/


---结果基于pkg1的procedure top_p变为了invalid
col name format a15
col referenced_name format a30
col owner format a15
col type format a15
set linesize 150
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='TOP_P';


OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
AD              TOP_P           PROCEDURE       AD                             PKG1                           PACKAGE            INVALID
AD              TOP_P           PROCEDURE       SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            INVALID
AD              TOP_P           PROCEDURE       SYS                            STANDARD                       PACKAGE            INVALID


---恢复top_p为valid
exec DBMS_UTILITY.VALIDATE(owner=>'AD',objname=>'TOP_P',namespace=>1);


select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='TOP_P';
OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
AD              TOP_P           PROCEDURE       AD                             PKG1                           PACKAGE            VALID
AD              TOP_P           PROCEDURE       SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID
AD              TOP_P           PROCEDURE       SYS                            STANDARD                       PACKAGE            VALID


---再次调整pkg1加入一行procedure p3，这次加在最后，再看一下top_p的状态是否就不会变为invalid了
create or replace package pkg1 is
function f1 return varchar2;
function f2 return varchar2;
procedure p2(v2 varchar2);
procedure p1(v1 varchar2);
procedure p3(v3 varchar2);
end;
/


---果然加最后不影响，不能打乱原来的顺序
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='TOP_P';
OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
AD              TOP_P           PROCEDURE       AD                             PKG1                           PACKAGE            VALID
AD              TOP_P           PROCEDURE       SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID
AD              TOP_P           PROCEDURE       SYS                            STANDARD                       PACKAGE            VALID





///  创建一个视图后，修改基表中被reference的字段类型从varchar2改成number，观察视图是否会失效

---创建测试表
drop table btab2;
create table btab2 tablespace ts_pub as select * from all_users;


drop view vtab2;
create view vtab2 as select * from btab2  where user_id>100;
col name format a15
col referenced_name format a30
col owner format a15
col type format a15
set linesize 150
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='VTAB2';


OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
LAST_DDL_TIME
-----------------
AD              VTAB2           VIEW            AD                             BTAB2                          TABLE              VALID
20150105 02:29:50


---修改基表字段类型
alter table btab2 modify (user_id varchar2(50));


ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


---先清空btab2表
truncate table btab2;


---修改btab2中的字段
alter table btab2 modify (user_id varchar2(50));


---vtab2状态变为invalid了，因为user_id从number变为了varchar2，类型这是两个完全不同的类型
SQL> select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='VTAB2';


OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
LAST_DDL_TIME
-----------------
AD              VTAB2           VIEW            AD                             BTAB2                          TABLE              INVALID
20150105 02:29:50


---查询vtab2，触发自动重编译，VTAB2回到valid状态
SQL> select * from vtab2;


no rows selected


SQL> select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='VTAB2';


OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
LAST_DDL_TIME
-----------------
AD              VTAB2           VIEW            AD                             BTAB2                          TABLE              VALID
20150105 02:34:46


---修改username类型，从VARCHAR2(30)改为VARCHAR2(300)，观察会否引起vtab2变成invalid
SQL> desc btab2;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 USERNAME                                                                            NOT NULL VARCHAR2(30)
 USER_ID                                                                             NOT NULL VARCHAR2(50)
 CREATED                                                                             NOT NULL DATE


alter table btab2 modify (username varchar2(300));


---vtab2变成了invalid，看来同样是varchar2，长度不同也会引起invalid，看来要使view不失效，修改前后的表字段长度也必须一样
SQL> select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name='VTAB2';


OWNER           NAME            TYPE            REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE    DEPENDE
--------------- --------------- --------------- ------------------------------ ------------------------------ ------------------ -------
LAST_DDL_TIME
-----------------
AD              VTAB2           VIEW            AD                             BTAB2                          TABLE              INVALID
20150105 02:34:46



///  procedure里包含一个view、一个synonym，对view、synonym进行重建后，是否会使procedure失效的测试

---创建测试所需的table、view、synonym
drop synonym syn_btab1;
drop view vtab3;
drop table btab1;
drop table btab3;


create table btab1 as select * from all_users;
create table btab3 as select * from dict where rownum<10;


SQL> desc btab3;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                         VARCHAR2(30)
 COMMENTS                                           VARCHAR2(4000)


SQL> desc btab1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 CREATED                                   NOT NULL DATE
 
create or replace synonym syn_btab1 for btab1;
create or replace view vtab3 as select * from btab3;


col name format a15
col referenced_name format a30
col REFERENCED_OWNER format a10
col owner format a15
col type format a15
set linesize 150
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('SYN_BTAB1','VTAB3');
OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              SYN_BTAB1       SYNONYM         AD         BTAB1                          TABLE              VALID   20150105 06:33:30
AD              VTAB3           VIEW            AD         BTAB3                          TABLE              VALID   20150105 06:33:30

---创建procedure包含对刚才所建view、synonym的引用
create or replace procedure prc1 is
var1 vtab3%rowtype;
var2 syn_btab1%rowtype;
begin
dbms_output.put_line('a');
end;
/


---prc1状态为valid，prc1与view、synonym的依赖关系
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');
OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            VALID   20150105 06:33:52


---重建synonym指向与btab1表结构完全相同的btab1_copy表
create table btab1_copy as select * from btab1 where 1=2;


SQL> desc btab1_copy
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 USERNAME                                                                            NOT NULL VARCHAR2(30)
 USER_ID                                                                             NOT NULL NUMBER
 CREATED                                                                             NOT NULL DATE
 
create or replace synonym syn_btab1 for btab1_copy;


---procedure prc1状态依然为valid
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');
OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            VALID   20150105 06:33:52
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            VALID   20150105 06:33:52


---修改btab1_copy的字段长度
alter table btab1_copy modify (username varchar2(300));


Table altered.


---因为修改后btab1_copy表结构和btab1不一致，查看procedure prc1状态变为失效了
SQL> select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');


OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            INVALID 20150105 06:33:52
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               INVALID 20150105 06:33:52
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            INVALID 20150105 06:33:52
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            INVALID 20150105 06:33:52
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            INVALID 20150105 06:33:52


---执行prc1，将prc1重新置为有效
exec prc1;


select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');


OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            VALID   20150105 06:42:12


---和synonym类似，view里的字段类型发生变化也会导致procedure失效
alter table btab3 add (c3 varchar2(20));


select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');


OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            VALID   20150105 06:42:12
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            VALID   20150105 06:42:12


create or replace view vtab3 as select * from btab3;


---因为view底下的基表增加了一个字段所以prc1变成invalid
select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');
OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            INVALID 20150105 06:42:12
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               INVALID 20150105 06:42:12
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            INVALID 20150105 06:42:12
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            INVALID 20150105 06:42:12
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            INVALID 20150105 06:42:12


exec prc1;


select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');
OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            VALID   20150105 07:00:49
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               VALID   20150105 07:00:49
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID   20150105 07:00:49
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            VALID   20150105 07:00:49
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            VALID   20150105 07:00:49


---修改基表的字段类型也会使prc1变为valid
alter table btab3 modify (c3 varchar2(100));


select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');


OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            INVALID 20150105 07:00:49
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               INVALID 20150105 07:00:49
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            INVALID 20150105 07:00:49
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            INVALID 20150105 07:00:49
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            INVALID 20150105 07:00:49


exec prc1;


---重建view指向一张新表，新表的字段类型完全等同于旧表，prc1不会失效
create table btab33 as select * from btab3;


create or replace view vtab3 as select * from btab33;


SQL> select dp.owner,dp.name,dp.type,dp.referenced_owner,dp.referenced_name,dp.referenced_type,uo.status dependent_status,uo.last_ddl_time from dba_dependencies dp,dba_objects uo where uo.object_name=dp.name and dp.name in ('PRC1');


OWNER           NAME            TYPE            REFERENCED REFERENCED_NAME                REFERENCED_TYPE    DEPENDE LAST_DDL_TIME
--------------- --------------- --------------- ---------- ------------------------------ ------------------ ------- -----------------
AD              PRC1            PROCEDURE       AD         SYN_BTAB1                      SYNONYM            VALID   20150105 07:02:36
AD              PRC1            PROCEDURE       AD         VTAB3                          VIEW               VALID   20150105 07:02:36
AD              PRC1            PROCEDURE       SYS        SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE            VALID   20150105 07:02:36
AD              PRC1            PROCEDURE       PUBLIC     DBMS_OUTPUT                    SYNONYM            VALID   20150105 07:02:36
AD              PRC1            PROCEDURE       SYS        STANDARD                       PACKAGE            VALID   20150105 07:02:36


总结：将synonym、view放在procedure里虽然能够起到封装的作用，最大程度的减少table的变化对于procedure有效性的影响，但有一个前提是synonym、view在重建前后所返回的字段类型必须保持一致，否则还是有可能引起procedure失效。
如果在package增加一个procedure或者function定义，按先来后到的顺序加在最后面，可以避免dependent object变为失效状态
变更object之前可以参考MOS 756350.1的方法检查是否存在与该object有关的依赖关系

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1377827/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1377827/