こちらの記事は2015年09月14日にQiitaに投稿したものを移植したものとなります。

Qiitaからの移植に当たって内容の精査は行っていません。
従って、記事は当時の私の知識や当時の技術そのままです。

目的


ORACLEで再帰SQLを使用して特定のデータベースオブジェクトの全ての依存関係を調べる。

基本


  • DBA_DEPENDENCIESを確認することで、依存関係を確認できます。
項目名説明備考
OWNER参照側のオブジェクトの所有者
NAME参照側のオブジェクト名
TYPE参照側のオブジェクトの種類
REFERENCED_OWNER参照される側のオブジェクトの所有者
REFERENCED_NAME参照される側のオブジェクトの名前
REFERENCED_TYPE参照される側のオブジェクトの種類
REFERENCED_LINK_NAME親オブジェクトへのリンク名(リモートの場合)ALL_DEPENDENCIESの場合のみ
  • 「USER_DEPENDENCIES」の場合、現行スキーマが対象。
  • 「DBA_DEPENDENCIES」の場合、現行DBが対象。
  • 「ALL_DEPENDENCIES」の場合、全DB対象。

以下のSQLが基になります。

1
2
3
4
5
6
7
SELECT *
FROM DBA_DEPENDENCIES
WHERE OWNER = 'scott' --参照するオブジェクトの所有者
AND REFERENCED_OWNER = 'scott' --参照されるオブジェクトの所有者
AND REFERENCED_TYPE = 'TABLE' --参照されるオブジェクトの種類
AND NAME = 'EMP' --参照するオブジェクトの名前
ORDER BY TYPE,NAME,REFERENCED_TYPE,REFERENCED_NAME

しかし、これでは全ての依存関係を確認することはできません。(親から見た1つ下の子までしか確認できない…)ですので、再帰SQLを使用して指定したオブジェクトの全ての依存関係(全てのノード)を調べます。

再帰SQLの書き方は下記を参考にしました。

第31回 再帰的問合せについて
木構造なデータの探索

指定したオブジェクトが参照している全てのオブジェクトを取得するSQL


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/* 呼び出しているもの一覧 */
WITH TEMP(LVL
,NAME
,TYPE
,REFERENCED_NAME
,REFERENCED_TYPE
,REFERENCED_OWNER
)
AS (
SELECT 0
,NAME
,TYPE
,REFERENCED_NAME
,REFERENCED_TYPE
,REFERENCED_OWNER
FROM DBA_DEPENDENCIES
WHERE NAME = '呼び出し元のオブジェクト名'
AND REFERENCED_OWNER != 'SYS'
AND REFERENCED_OWNER != 'PUBLIC'
--AND REFERENCED_OWNER = 'OWNER' --呼び出し先のオブジェクトの所有者を指定する場合
UNION ALL
SELECT T.LVL+1
,DBAD2.NAME
,DBAD2.TYPE
,DBAD2.REFERENCED_NAME
,DBAD2.REFERENCED_TYPE
,DBAD2.REFERENCED_OWNER
FROM DBA_DEPENDENCIES DBAD2
,TEMP T
WHERE DBAD2.NAME = T.REFERENCED_NAME
AND DBAD2.REFERENCED_OWNER != 'SYS'
AND DBAD2.REFERENCED_OWNER != 'PUBLIC'
AND OWNER = 'OWNER' --呼び出しているオブジェクトの所有者を指定
)
SEARCH DEPTH FIRST BY NAME SET ORDER1 --深さ優先で探索する場合
--SEARCH BREADTH FIRST BY NAME SET ORDER1 --幅優先で探索する場合
CYCLE NAME SET ISLOOP TO 'Y' DEFAULT 'N'

SELECT LVL AS 階層
,NAME AS 参照元
,TYPE AS 参照元のタイプ
,REFERENCED_NAME AS 参照先
,REFERENCED_TYPE AS 参照先のタイプ
,REFERENCED_OWNER AS 参照先の所有者
,ORDER1 AS ノード探索順
FROM TEMP
WHERE ISLOOP = 'N' --訪問済みノードを表示しない
--AND LVL = 1 --階層を指定する場合
ORDER BY ORDER1,NAME,LVL,REFERENCED_TYPE;

特定オブジェクトを参照している全てのオブジェクトを取得するSQL


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/* 呼び出されている一覧 */
WITH TEMP (LVL
,REFERENCED_OWNER
,REFERENCED_NAME
,REFERENCED_TYPE
,NAME
,TYPE
)
AS (
SELECT 0
,REFERENCED_OWNER
,REFERENCED_NAME
,REFERENCED_TYPE
,NAME
,TYPE
FROM DBA_DEPENDENCIES
WHERE REFERENCED_NAME = '呼び出されているオブジェクト名'
AND REFERENCED_OWNER != 'SYS'
AND REFERENCED_OWNER != 'PUBLIC'
AND REFERENCED_OWNER = 'OWNER' --呼び出されているオブジェクトの所有者
UNION ALL
SELECT T.LVL+1
,DBAD2.REFERENCED_OWNER
,DBAD2.REFERENCED_NAME
,DBAD2.REFERENCED_TYPE
,DBAD2.NAME
,DBAD2.TYPE
FROM DBA_DEPENDENCIES DBAD2
,TEMP T
WHERE DBAD2.REFERENCED_NAME = T.NAME
AND DBAD2.REFERENCED_OWNER != 'SYS'
AND DBAD2.REFERENCED_OWNER != 'PUBLIC'
--AND OWNER = 'OWNER' --呼び出し先を特定の所有者に絞り込みたい場合
)
SEARCH DEPTH FIRST BY NAME SET ORDER1 --深さ優先で探索する場合
--SEARCH BREADTH FIRST BY NAME SET ORDER1 --幅優先で探索する場合
CYCLE NAME SET ISLOOP TO 'Y' DEFAULT 'N'

SELECT LVL AS 階層
,REFERENCED_NAME AS 参照先
,REFERENCED_TYPE AS 参照先のタイプ
,REFERENCED_OWNER AS 参照先の所有者
,NAME AS 参照元
,TYPE AS 参照元のタイプ
,ORDER1 AS ノード探索順
FROM TEMP
WHERE ISLOOP = 'N' --訪問済みノードを表示しない
--AND LVL = 1 --階層を指定する場合
ORDER BY LVL,ORDER1,NAME,REFERENCED_TYPE;