【Oracle DB】依存関係のあるDBオブジェクトの階層情報を抽出する

やりたいこと

Oracle Database内で依存関係を持つオブジェクトのストラクチャ型の部品表（BOM）が欲しい。

環境

Oracle Database 23ai Free

DBオブジェクトの相関図

ぐっちゃぐちゃです。ですが、私の経験上ではこのくらい複雑な構成も稀に見ます。

DDL

上記相関図を実現するべく、以下のスクリプトを実行します。

CREATE TABLE TABLE_A (COL_A1 NUMBER PRIMARY KEY, COL_A2 VARCHAR2(10 CHAR));
CREATE TABLE TABLE_B (COL_B1 NUMBER PRIMARY KEY, COL_B2 NUMBER NOT NULL, COL_B3 VARCHAR2(10 CHAR));
CREATE VIEW VIEW_C AS SELECT A.COL_A1 AS COL_C1, B.COL_B1 AS COL_C2, B.COL_B3 AS COL_C3 FROM TABLE_A A JOIN TABLE_B B ON A.COL_A1 = B.COL_B2;
CREATE TABLE TABLE_D (COL_D1 NUMBER PRIMARY KEY, COL_D2 VARCHAR2(10 CHAR));
CREATE SYNONYM SYNONYM_E FOR TABLE_D;
CREATE TABLE TABLE_F (COL_F1 DATE NOT NULL, COL_F2 NUMBER NOT NULL, COL_F3 NUMBER NOT NULL);
CREATE TABLE TABLE_G (COL_G1 DATE NOT NULL, COL_G2 NUMBER NOT NULL, COL_G3 NUMBER NOT NULL);
CREATE VIEW VIEW_H AS SELECT FG.COL_H1, FG.COL_H2, FG.COL_H3, E.COL_D2 AS COL_H4 FROM (SELECT COL_F1 AS COL_H1, COL_F2 AS COL_H2, COL_F3 AS COL_H3 FROM TABLE_F UNION ALL SELECT COL_G1 AS COL_H1, COL_G2 AS COL_H2, COL_G3 AS COL_H3 FROM TABLE_G) FG JOIN SYNONYM_E E ON E.COL_D1 = FG.COL_H2;
CREATE SYNONYM SYNONYM_I FOR VIEW_H;
CREATE VIEW VIEW_J AS SELECT COL_C2 AS COL_J1, COL_H4 AS COL_J2 FROM VIEW_C C LEFT JOIN SYNONYM_I I ON I.COL_H3 = C.COL_C1;

SQL

BOM出力の実践です。以下のクエリで検索を実行。
ポイントは階層問い合わせを利用する点です。

query

SELECT
    LEVEL
   ,referenced_type
   ,CONNECT_BY_ROOT name || SYS_CONNECT_BY_PATH(TO_CHAR(referenced_name), '/') AS structure
   ,CONNECT_BY_ISLEAF AS is_leaf
FROM
    user_dependencies
START WITH
    name = 'VIEW_J'
CONNECT BY PRIOR
    referenced_name = name
;

クエリを実行すると以下の結果が得られます。VIEW_Jが依存している全てのオブジェクトが出力できています。IS_LEAF列が"1"になっているデータが末端になるオブジェクトです。

result

     LEVEL REFERENCED_TYPE     STRUCTURE                                             IS_LEAF
---------- ------------------- -------------------------------------------------- ----------
         1 SYNONYM             VIEW_J/SYNONYM_I                                            0
         2 VIEW                VIEW_J/SYNONYM_I/VIEW_H                                     0
         3 SYNONYM             VIEW_J/SYNONYM_I/VIEW_H/SYNONYM_E                           0
         4 TABLE               VIEW_J/SYNONYM_I/VIEW_H/SYNONYM_E/TABLE_D                   1
         3 TABLE               VIEW_J/SYNONYM_I/VIEW_H/TABLE_F                             1
         3 TABLE               VIEW_J/SYNONYM_I/VIEW_H/TABLE_G                             1
         1 VIEW                VIEW_J/VIEW_C                                               0
         2 TABLE               VIEW_J/VIEW_C/TABLE_A                                       1
         2 TABLE               VIEW_J/VIEW_C/TABLE_B                                       1

9行が選択されました。 

見た目の好みもあると思いますので、適宜アレンジしながら使っていただければと思います。
本例では階層問い合わせで使えるCONNECT_BY_ROOT演算子やLEVEL、CONNECT_BY_ISLEAF擬似列などを参考例として使用しています。
スキーマを跨いだ検索をしたい場合はall_dependenciesを参照しましょう。