ORACLEの機能を使って、オブジェクト間の依存関係を調べる

• 各DBオブジェクトがどのような依存関係を持っているか調べるスクリプト
• hogehoge_tbl が依存関係を調べたいテーブルの名前、のパターンのSQL

SQL

select
  referenced_owner || '.' || referenced_name as RefObj
, type, owner || '.' || name                 as Object
, decode(Level,1,'直接','間接')              as "dependency"
, substr(sys_connect_by_path(referenced_name,'←'),2) || '←' || name as "dependency_list"
from
  all_dependencies
    start with referenced_name = upper('hogehoge_tbl') -- 依存関係を調べたいオブジェクトの名前
        connect by
              prior owner = referenced_owner        
          and prior name  = referenced_name       
          and prior type  = referenced_type 
order siblings by
  type
, owner
, name
/