ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dba_dependencies

dba_dependencies

原创 Linux操作系统 作者:is.x 时间:2011-12-15 01:09:48 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: sql profile
请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    244965