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有关的依赖关系
扫一扫
2355
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
