查找当前用户的所有数据库对象的依赖关系

1.我们查看数据库对象依赖关系一般是用dba_dependencies视图来查看。

这里我们使用对象依赖树utldtree来查看.

首先建立依赖关系表：

CREATE TABLE OB_DEP(owner varchar2(20),--用户名

                       type varchar2(30), --对象类型,如表、存储过程、函数等

                       name varchar2(200), --对象名称

                       DEPDESC varchar2(2000)--依赖关系

                                               );

然后安装utldtree包(对象依赖树)

SQL> @F:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utldtree.sql

先测试一下：

--查看存储deptree_fill结构：
SQL> desc deptree_fill
PROCEDURE deptree_fill
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 TYPE                           CHAR                    IN
 SCHEMA                         CHAR                    IN
 NAME                           CHAR                    IN

SQL> SET SERVEROUTPUT ON
SQL> exec deptree_fill('TABLE','SCOTT','EMP');

PL/SQL 过程已成功完成。

SQL> SELECT * FROM IDEPTREE;

DEPENDENCIES
-----------------------------------------------
   PACKAGE BODY SYS.EMP_TEST
   FUNCTION SYS.TEST
   PROCEDURE SYS.UPD_SHIPDATE
TABLE SCOTT.EMP

------------------------
--IDEPTREE视图即保存了用户SCOTT的表EMP所依赖的对象(emp_test包、test函数、UPD_SHIPDATE过程)

现在我们来建立存储过程实现将当前用户的所有数据库对象插入到先前建立的依赖关系表中,存储过程如下:

  -- 寻找数据库所有对象的依赖关系
  CREATE OR REPLACE PROCEDURE DEPEND_RELATIONSHIP AS
  -- 定义第一个游标从Dba_Objects取用户、对象类型、对象名称
  CURSOR CUR_OBJECT IS
    SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM USER_OBJECTS;

  CUR01 CUR_OBJECT%ROWTYPE;

  -- 定义第二个游标从依赖树表中取依赖信息
  CURSOR CUR_DEP IS
    SELECT DEPENDENCIES FROM IDEPTREE;

  CUR02 CUR_DEP%ROWTYPE;

  --定义保存第一个游标的值的各个变量
  V_OWNER DBA_OBJECTS.OWNER%TYPE;
  V_TYPE  DBA_OBJECTS.OBJECT_TYPE%TYPE;
  V_NAME  DBA_OBJECTS.OBJECT_NAME%TYPE;

  V_DEPDESC OB_DEP.DEPDESC%TYPE;

BEGIN

  V_DEPDESC := '';
  SELECT USER INTO v_owner FROM dual; --获取当前用户名
  --打开游标CUR_OBJECT取用户、对象类型、对象名称
  OPEN CUR_OBJECT;
  LOOP
  
    FETCH CUR_OBJECT
      INTO CUR01;
    EXIT WHEN CUR_OBJECT%NOTFOUND;
    
    V_TYPE  := CUR01.OBJECT_TYPE;
    V_NAME  := CUR01.OBJECT_NAME;
  
    DEPTREE_FILL(V_TYPE, V_OWNER, V_NAME);
  
    -- 打开游标CUR_DEP,插入依赖信息
    OPEN CUR_DEP;
    LOOP
      FETCH CUR_DEP
        INTO CUR02;
      EXIT WHEN CUR_DEP%NOTFOUND;
      --获取依赖信息,有依赖关系的都是多条数据,没有依赖关系就是一条(它自己本身)
      V_DEPDESC := CUR02.DEPENDENCIES;
    
      INSERT/*+append */ INTO OB_DEP VALUES (V_OWNER, V_TYPE, V_NAME, V_DEPDESC);
    
    END LOOP;
    CLOSE CUR_DEP;
  
  END LOOP;
  CLOSE CUR_OBJECT;

  COMMIT;

  --出错处理
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error Occur:' || SQLCODE || ' ' || SQLERRM);
    ROLLBACK;
  
END DEPEND_RELATIONSHIP;

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

请注意:当数据库对象非常多的时候(大概超过2000个),执行该存储过程会很慢。