Oracle DBA_DEPENDENCIES

最新推荐文章于 2022-01-07 16:52:11 发布

原创最新推荐文章于 2022-01-07 16:52:11 发布 · 8.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#dependencies #oracle #object #user #sql #null

Oracle 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了在数据库中视图与表之间的依赖关系，并通过具体示例展示了如何创建视图及视图对底层表的依赖性。当底层表结构发生变化时，可能会导致视图失效。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当一个object A 定义中引用了object B, 则说明object A依赖于object B。

比如，创建视图employees是通过查询子句查询表emp而来的，那么employees就依赖于emp。employees引用了emp表中的所有列，当修改emp表中的列时，会导致视图employees失效：

("Compiled with errors", "Invalid", "Unauthorized"在user_objects中都表示为 "Invalid")

SQL> conn scott/tiger;
Connected.
SQL> create view employees
  2  as
  3  select * from emp;

View created.

SQL> select object_name, status from user_objects where object_type = 'VIEW';

OBJECT_NAME
--------------------------------------------------------------------------------

STATUS
--------------
EMPLOYEES
VALID


SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> alter table emp modify ename varchar2(20);

Table altered.

SQL> select object_name, status from user_objects where object_type = 'VIEW';

OBJECT_NAME
--------------------------------------------------------------------------------

STATUS
--------------
EMPLOYEES
INVALID


SQL>

静态数据字典视图DBA_DEPENDENCIES (USER_DEPENDENCIES，ALL_DEPENDENCIES) 描述各schema objects之间的相互依赖关系。

SQL> desc dba_dependencies;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------

 OWNER                                     NOT NULL VARCHAR2(30)
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(18)
 REFERENCED_OWNER                                   VARCHAR2(30)
 REFERENCED_NAME                                    VARCHAR2(64)
 REFERENCED_TYPE                                    VARCHAR2(18)
 REFERENCED_LINK_NAME                               VARCHAR2(128)
 DEPENDENCY_TYPE                                    VARCHAR2(4)

SQL> desc user_dependencies;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------

 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(18)
 REFERENCED_OWNER                                   VARCHAR2(30)
 REFERENCED_NAME                                    VARCHAR2(64)
 REFERENCED_TYPE                                    VARCHAR2(18)
 REFERENCED_LINK_NAME                               VARCHAR2(128)
 SCHEMAID                                           NUMBER
 DEPENDENCY_TYPE                                    VARCHAR2(4)

SQL> select * from dba_dependencies
  2  where owner = 'SCOTT';

OWNER NAME      TYPE      REFER REFERENCED REFERENC REFERENCED_LINK DEPENDEN
----- --------- --------- ----- ---------- -------- --------------- --------
SCOTT EMPLOYEES VIEW      SCOTT EMP        TABLE                    HARD