如何查詢儲存過程procedure所依賴物件的有效性--dba_dependencies

網友問題

 --查一個儲存過程所依敕對像的有效性如何?查?親們.

結論

1,dba_dependencies記錄當前物件與依賴的基物件之間的關係
2,name列為當前物件，而referenced_name為依賴的基物件
3,透過上述列可以與dba_objects關係，獲取依賴的基物件的狀態是否有效或無效，然後採取針對性措施

測試

SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


SQL> show user
USER is "SCOTT"
SQL> create table t_base(a int);


Table created.


SQL> select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependencies where referenced_type='TABLE' and REFERENCED_NAME='T_BASE';


no rows selected


SQL> create or replace view v_t_base as  select count(*) as cnt from t_base;


View created.


SQL> select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependencies where referenced_type='TABLE' and REFERENCED_NAME='T_BASE';


OWNER      NAME                           TYPE            REFERENCED_OWNER     REFERENCED_NAME                                    REFERENCED_TYPE
---------- ------------------------------ --------------- -------------------- -------------------------------------------------- ------------------------------------
SCOTT      V_T_BASE                       VIEW            SCOTT                T_BASE                                             TABLE




SQL> select object_name,status from dba_objects where owner='SCOTT' and object_name='V_T_BASE';


OBJECT_NAME                                        STATUS
-------------------------------------------------- --------------------------------------------------
V_T_BASE                                           VALID


SQL> create or replace procedure proc_t_base
  2  as
  3  v_cnt pls_integer;
  4  begin
  5  select count(a) into v_cnt from t_base;
  6  end;
  7  /


Procedure created.


SQL> select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependencies where referenced_type='TABLE' and REFERENCED_NAME='T_BASE';


OWNER      NAME                           TYPE            REFERENCED_OWNER     REFERENCED_NAME                                    REFERENCED_TYPE
---------- ------------------------------ --------------- -------------------- -------------------------------------------------- ------------------------------------
SCOTT      PROC_T_BASE                    PROCEDURE       SCOTT                T_BASE                                             TABLE
SCOTT      V_T_BASE                       VIEW            SCOTT                T_BASE                                             TABLE


SQL> 


SQL> select object_name,status from dba_objects where owner='SCOTT' and object_name='T_BASE';


OBJECT_NAME                                        STATUS
-------------------------------------------------- --------------------------------------------------
T_BASE                                             VALID