dba_dependencies

当一个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]  view plain copy

1. SQL> conn scott/tiger;  
2. Connected.  
3. SQL> create view employees  
4.   2  as  
5.   3  select * from emp;  
6.   
7. View created.  
8.   
9. SQL> select object_name, status from user_objects where object_type = 'VIEW';  
10.   
11. OBJECT_NAME  
12. --------------------------------------------------------------------------------  
13.   
14. STATUS  
15. --------------  
16. EMPLOYEES  
17. VALID  
18.   
19.   
20. SQL> desc emp;  
21.  Name                                      Null?    Type  
22.  ----------------------------------------- -------- ----------------------------  
23.   
24.  EMPNO                                     NOT NULL NUMBER(4)  
25.  ENAME                                              VARCHAR2(10)  
26.  JOB                                                VARCHAR2(9)  
27.  MGR                                                NUMBER(4)  
28.  HIREDATE                                           DATE  
29.  SAL                                                NUMBER(7,2)  
30.  COMM                                               NUMBER(7,2)  
31.  DEPTNO                                             NUMBER(2)  
32.   
33. SQL> alter table emp modify ename varchar2(20);  
34.   
35. Table altered.  
36.   
37. SQL> select object_name, status from user_objects where object_type = 'VIEW';  
38.   
39. OBJECT_NAME  
40. --------------------------------------------------------------------------------  
41.   
42. STATUS  
43. --------------  
44. EMPLOYEES  
45. INVALID  
46.   
47.   
48. SQL>  

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

[sql]  view plain copy

1. SQL> desc dba_dependencies;  
2.  Name                                      Null?    Type  
3.  ----------------------------------------- -------- --------------  
4.   
5.  OWNER                                     NOT NULL VARCHAR2(30)  
6.  NAME                                      NOT NULL VARCHAR2(30)  
7.  TYPE                                               VARCHAR2(18)  
8.  REFERENCED_OWNER                                   VARCHAR2(30)  
9.  REFERENCED_NAME                                    VARCHAR2(64)  
10.  REFERENCED_TYPE                                    VARCHAR2(18)  
11.  REFERENCED_LINK_NAME                               VARCHAR2(128)  
12.  DEPENDENCY_TYPE                                    VARCHAR2(4)  

 

[sql]  view plain copy

1. SQL> desc user_dependencies;  
2.  Name                                      Null?    Type  
3.  ----------------------------------------- -------- -----------------  
4.   
5.  NAME                                      NOT NULL VARCHAR2(30)  
6.  TYPE                                               VARCHAR2(18)  
7.  REFERENCED_OWNER                                   VARCHAR2(30)  
8.  REFERENCED_NAME                                    VARCHAR2(64)  
9.  REFERENCED_TYPE                                    VARCHAR2(18)  
10.  REFERENCED_LINK_NAME                               VARCHAR2(128)  
11.  SCHEMAID                                           NUMBER  
12.  DEPENDENCY_TYPE                                    VARCHAR2(4)  

 

[sql]  view plain copy

1. SQL> select * from dba_dependencies  
2.   2  where owner = 'SCOTT';  
3.   
4. OWNER NAME      TYPE      REFER REFERENCED REFERENC REFERENCED_LINK DEPENDEN  
5. ----- --------- --------- ----- ---------- -------- --------------- --------  
6. SCOTT EMPLOYEES VIEW      SCOTT EMP        TABLE                    HARD