ORATEA

通过数据字典查看PLSQL/VIEW的对象依赖

查询PLSQL或VIEW中的倚赖对象

SELECT r3.owner,r3.object_name procedure_name,r3.object_type,
r1.ORDER# seq,
(SELECT owner FROM dba_objects WHERE object_id = P_OBJ#) object_owner,
(SELECT object_name FROM DBA_OBJECTS WHERE object_id = P_OBJ#) object_name,
(SELECT object_type FROM DBA_OBJECTS WHERE object_id = P_OBJ#) object_type,
r2.TYPES,
DECODE(r2.TYPES,
9,’SELECT I’,
6,’INSERT’,
3,’DELETE’,
10,’UPDATE’,
12,’PROCEDURE’,
13,’SELECT II’,
‘UNKNOW’) op_type,
DECODE(r2.TYPES,
9,0,
6,1,
3,1,
10,1,
12,2,
13,0,
-1) in_out
FROM SYS.Dependency$ r1, sys.access$ r2, dba_objects r3
WHERE r1.D_OBJ# = r2.D_OBJ#
AND r1.ORDER# = r2.ORDER#
AND r1.D_OBJ# = r3.object_id
AND (r3.owner ,r3.object_name ) IN
(SELECT owner,object_name
FROM DBA_OBJECTS
WHERE owner = ‘ETL’
AND object_type IN (’PROCEDURE’,'VIEW’)
AND object_name = ‘P_CN_LOAN_SIGNUP_H_SUMDT0′);

OWNER PROCEDURE_NAME OBJECT_TYP SEQ OBJECT_OWN OBJECT_NAME OBJECT_TYP TYPES OP_TYPE IN_OUT
—– —————————— ———- — ———- —————————— ———- ———- ——— ———-
ETL P_CN_LOAN_SIGNUP_H_SUMDT0 PROCEDURE 0 SYS STANDARD PACKAGE 12 PROCEDURE 2
ETL P_CN_LOAN_SIGNUP_H_SUMDT0 PROCEDURE 1 CNDSS CN_LOAN_SIGNUP_H_SUMDT0 TABLE 3 DELETE 1
ETL P_CN_LOAN_SIGNUP_H_SUMDT0 PROCEDURE 2 CNODS LOAN_SIGNUP TABLE 9 SELECT I 0
ETL P_CN_LOAN_SIGNUP_H_SUMDT0 PROCEDURE 3 CNODS LOAN_JOINT_MEMBER TABLE 9 SELECT I 0
ETL P_CN_LOAN_SIGNUP_H_SUMDT0 PROCEDURE 4 CNODS LOAN_JOINT TABLE 9 SELECT I 0
ETL P_CN_LOAN_SIGNUP_H_SUMDT0 PROCEDURE 1 CNDSS CN_LOAN_SIGNUP_H_SUMDT0 TABLE 6 INSERT 1

字段说明：
OWNER ：分析对象用户
PROCEDURE_NAME：分析过程名
OBJECT_TYPE ：分析对象类别
SEQ ：依赖序列号
OBJECT_OWNER ：依赖对象用户
OBJECT_NAME ：依赖对象名
OBJECT_TYPE ：依赖对象类别
TYPE ：依赖类别
OP_TYPE ：倚赖类别名
IN_OUT ：输入输出标志