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个),执行该存储过程会很慢。