dba_dependencies

DBA_DEPENDENCIES，用来显示数据库中各对象的依赖关系。有时候在进行数据库对象清理时，这个视图可以很好地反映当前清理对象的依赖关系，用起来很方便。

 

DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.

 

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object
NAME	VARCHAR2(30)	NOT NULL	Name of the object
TYPE	VARCHAR2(17)		Type of object
REFERENCED_OWNER	VARCHAR2(30)		Owner of the parent object
REFERENCED_NAME	VARCHAR2(64)		Type of parent object
REFERENCED_TYPE	VARCHAR2(17)		Type of referenced object
REFERENCED_LINK_NAME	VARCHAR2(128)		Name of the link to the parent object (if remote)
SCHEMAID	NUMBER		ID of the current schema
DEPENDENCY_TYPE	VARCHAR2(4)		Whether the dependency is a REF dependency (REF) or not (HARD)

 

简单测试一下，首先创建一张测试表

 

SQL> create table xsfree(num number);

 

Table created.

 

创建触发器

 

SQL> create or replace trigger trig_xsfree

  2     before insert on xsfree

  3     for each row

  4     begin

  5             :new.num:=:new.num+1;

  6     end;

  7  /

 

Trigger created.

 

来看一下dba_dependencies视图

 

SQL> set linesize 300 pagesize 300

SQL> col REFERENCED_OWNER format a20

SQL> col owner format a10

SQL> col name format a20

SQL> col referenced_name format a20

SQL> select owner,name,type,referenced_owner,referenced_name,referenced_type

  2  from dba_dependencies where wner='TEST';

 

OWNER      NAME                 TYPE              REFERENCED_OWNER     REFERENCED_NAME      REFERENCED_TYPE

---------- -------------------- ----------------- -------------------- -------------------- -----------------

TEST       TRIG_XSFREE          TRIGGER           SYS                  STANDARD             PACKAGE

TEST       TRIG_XSFREE          TRIGGER           TEST                 XSFREE               TABLE

 

从这里可以看到，触发器trig_xsfree和表的依赖关系清晰显示。

 

再试试创建一个视图

 

SQL> create view v_xsfree as select * from xsfree;

 

View created.

 

SQL>

SQL>

SQL> select owner,name,type,referenced_owner,referenced_name,referenced_type

  2  from dba_dependencies where wner='TEST';

 

OWNER      NAME                 TYPE              REFERENCED_OWNER     REFERENCED_NAME      REFERENCED_TYPE

---------- -------------------- ----------------- -------------------- -------------------- -----------------

TEST       TRIG_XSFREE          TRIGGER           SYS                  STANDARD             PACKAGE

TEST       V_XSFREE             VIEW              TEST                 XSFREE               TABLE

TEST       TRIG_XSFREE          TRIGGER           TEST                 XSFREE               TABLE

 

 

现在将原来的表drop掉

 

SQL> drop table xsfree;

 

Table dropped.

 

依赖关系不存在，原来的视图状态变为INVALID

 

SQL> select owner,name,type,referenced_owner,referenced_name,referenced_type

  2  from dba_dependencies where wner='TEST';

 

no rows selected

 

SQL> col object_name format a30

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                    OBJECT_TYPE         STATUS

------------------------------ ------------------- -------

V_XSFREE                       VIEW                INVALID

 

SQL> drop view v_xsfree;

 

View dropped.

 

SQL> select object_name,object_type,status from user_objects;

 

no rows selected

 

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

转载于:http://blog.itpub.net/20750200/viewspace-713331/