10-112 階層問い合わせで依存性を追跡

SQLパズル

依存性リスト(TESTスキーマ)

TableA                           TableBBB
 ＼                               ＼
    ViewB                            ViewBBBB
     ＼                               ＼
       ViewC                            ViewCCCC
     ／ ＼                                ／
   ／     ＼                            ／
 ViewD      ViewE                     ／
    ＼     ／ ＼                    ／
      ＼ ／     ＼                ／
       ViewF     ViewG          ／
                    ＼        ／
                      ＼    ／
                        ViewH


TableAとTableBBBに依存しているビュー(直接依存と間接依存)の、依存リスト
を出力する。

出力結果
REF            OBJECT        依存      依存リスト
-------------  ------------  --------  ------------------------------------------------
TEST.TableA    TEST.ViewB    直接依存  TableA←ViewB
TEST.ViewB     TEST.ViewC    間接依存  TableA←ViewB←ViewC
TEST.ViewC     TEST.ViewD    間接依存  TableA←ViewB←ViewC←ViewD
TEST.ViewD     TEST.ViewF    間接依存  TableA←ViewB←ViewC←ViewD←ViewF
TEST.ViewF     TEST.ViewG    間接依存  TableA←ViewB←ViewC←ViewD←ViewF←ViewG
TEST.ViewG     TEST.ViewH    間接依存  TableA←ViewB←ViewC←ViewD←ViewF←ViewG←ViewH
TEST.ViewC     TEST.ViewE    間接依存  TableA←ViewB←ViewC←ViewE
TEST.ViewE     TEST.ViewF    間接依存  TableA←ViewB←ViewC←ViewE←ViewF
TEST.ViewF     TEST.ViewG    間接依存  TableA←ViewB←ViewC←ViewE←ViewF←ViewG
TEST.ViewG     TEST.ViewH    間接依存  TableA←ViewB←ViewC←ViewE←ViewF←ViewG←ViewH
TEST.TableBBB  TEST.ViewBBB  直接依存  TableBBB←ViewBBB
TEST.ViewBBB   TEST.ViewCCC  間接依存  TableBBB←ViewBBB←ViewCCC
TEST.ViewCCC   TEST.ViewH    間接依存  TableBBB←ViewBBB←ViewCCC←ViewH

データ作成スクリプト

create table TableA as select 1 as ColA from dual;

create or replace view ViewB as select * from TableA;
create or replace view ViewC as select * from ViewB;
create or replace view ViewD as select * from ViewC;
create or replace view ViewE as select * from ViewC;

create or replace view ViewF as
select * from ViewD
union all select * from ViewE;

create or replace view ViewG as select * from ViewF;

create table TableBBB as select 1 as ColA from dual;
create or replace view ViewBBB as select * from TableBBB;
create or replace view ViewCCC as select * from ViewBBB;

create or replace view ViewH as
select * from ViewG
union all select * from ViewCCC;

SQL

select referenced_owner || '.' || referenced_name as Ref,
owner || '.' || name as Object,
decode(Level,1,'直接依存','間接依存') as 依存,
substr(sys_connect_by_path(referenced_name,'←'),2) || '←' || name as 依存リスト
from all_dependencies
where type = 'VIEW'
start with referenced_name in (upper('TableA'),upper('TableBBB'))
connect by prior owner = referenced_owner
       and prior name  = referenced_name
       and prior type  = referenced_type
order siblings by owner,name,type;

解説

階層問い合わせで、依存性を追跡してます。

---