如何查找存储过程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

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

转载于:http://blog.itpub.net/9240380/viewspace-1977913/