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有關的依賴關係

哪些操作易引起儲存過程失效？

相關文章