利用数据字典表dba_dependencies算出EDW中的依赖关系

关于对数据仓库依赖关系的整理，经历了三个阶段：09年仓库1期的时候，仓库跑批依赖关系的整理是靠手工完成的，工作烦琐，要细心、耐心才能做好；10年做仓库2期的时候，考虑依赖关系是否能自动化完成，结果根据all_source数据字典表通过相应的限制及筛选条件用SQL语句找出了其依赖关系；接着就是现在，因为使用TOAD时无意中发现ORACLE中是应该会有一个体现依赖关系的数据字典表，结果就有了今天这样的一个方案。

1、查找出需要排在前面跑批的存储过程

 select distinct replace(referenced_name, 'EDW_', 'P_') 需要排在前面的存储过程 from dba_dependencies where owner = 'EDW2' and type = 'PROCEDURE' and referenced_owner not in ('PUBLIC', 'SYS') and referenced_name not like 'TMP%' and referenced_type <> 'NON-EXISTENT' and referenced_name <> 'EDW_ETL_LOG_DETAIL' and name <> replace(referenced_name, 'EDW_', 'P_') and name like 'P_T%' and referenced_name like 'EDW_T%' and referenced_name not like 'EDW_T99%' order by 1;

2、整理EDW层依赖关系表

 select name EDW存储过程名, referenced_name 依赖的源表 from dba_dependencies where owner = 'EDW2' and type = 'PROCEDURE' and referenced_owner not in ('PUBLIC', 'SYS') and referenced_name not like 'TMP%' and referenced_type <> 'NON-EXISTENT' and referenced_name <> 'EDW_ETL_LOG_DETAIL' and name <> replace(referenced_name, 'EDW_', 'P_') and name like 'P_T%' and referenced_name not like 'EDW_T99%' and referenced_name not like 'EDW_MAP%' order by 1;

注：(1和2)两都组合能可以实现很多其他功能，如，查找依赖的依赖等。

3、统计用到的ODS源表

 select distinct referenced_name 依赖的ODS源表 from dba_dependencies where owner = 'EDW2' and type = 'PROCEDURE' and referenced_owner not in ('PUBLIC', 'SYS') and referenced_name not like 'TMP%' and referenced_name not like 'EDW%' and referenced_type = 'TABLE' and name like 'P_T%' order by 1;

4、找一个ODS源表被哪些存储过程使用

 select referenced_name ODS源表, name EDW存储过程名 from dba_dependencies where owner = 'EDW2' and type = 'PROCEDURE' and referenced_owner = 'EDW2' and name like 'P_T%' and referenced_name = &SRC_TAB_NAME order by 2;

注：条件如果是字符，最好带上两个单引号，以免报错。

5、总结

(1)、dba_dependencies中包含的依赖关系，包含编译通过及未通过存储过程依赖关系信息；

(2)、对技术的学习是无止境的，只要静下心来，都会有很多收获的。