Oracle 各スキーマがアクセスできるプロシージャ、パッケージ、ファンクション、トリガーなどの依存性リスト取得

list_dependencies.sql

--
-- 各スキーマがアクセスできるプロシージャ、パッケージ、ファンクション、トリガーなどの依存性リスト取得
--
-- 当然のことだが、OracleにSTOREされないSQL単体やPL/SQLの無名ブロックはこのリストには載らない。
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 250
SET PAGESIZE 50000
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL list_dependencies_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL list_dependencies_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..log
BREAK ON NAME ON TYPE
COLUMN NAME                  FORMAT A30
COLUMN TYPE                  FORMAT A17
COLUMN REFERENCED_NAME       FORMAT A45 HEADING "REFERENCED OWNER.NAME"
COLUMN REFERENCED_TYPE       FORMAT A12
COLUMN REFERENCED_LINK_NAME  FORMAT A15
COLUMN DEPENDENCY_TYPE       FORMAT A10
SELECT
    DP.NAME                                -- オブジェクトの名前
  , DP.TYPE                                -- オブジェクトのタイプ
  , DECODE(MO.STATUS, 'VALID', NULL, MO.STATUS) AS STATUS
  , MO.LAST_DDL_TIME                                                 -- 最後に更新された日次
  , NVL2(DP.REFERENCED_LINK_NAME, DP.REFERENCED_OWNER                -- 参照されるオブジェクトの所有者(リモート・オブジェクトの場合はリモートの所有者).
                                  || '.' || DP.REFERENCED_NAME       -- 参照されるオブジェクトの名前
                                  || '@' || DP.REFERENCED_LINK_NAME  -- 親オブジェクトへのリンク名(リモートの場合)
  , DP.REFERENCED_OWNER || '.' || DP.REFERENCED_NAME) AS REFERENCED_NAME
  , DP.REFERENCED_TYPE                     -- 参照されるオブジェクトの型
  , DECODE(RO.STATUS, 'VALID', NULL, RO.STATUS) AS R_STATUS
  , DECODE(DP.DEPENDENCY_TYPE, 'HARD', NULL, DP.DEPENDENCY_TYPE) AS DEPENDENCY_TYPE  -- 依存性がREF依存性であるか(REF)、そうでないか(HARD)
FROM USER_DEPENDENCIES DP
LEFT OUTER JOIN USER_OBJECTS MO  -- MY OBJECT
ON  MO.OBJECT_TYPE = DP.TYPE
AND MO.OBJECT_NAME = DP.NAME
LEFT OUTER JOIN USER_OBJECTS RO  -- REFERENCED OBJECT
ON  RO.OBJECT_TYPE = DP.REFERENCED_TYPE
AND RO.OBJECT_NAME = DP.REFERENCED_NAME
WHERE
    RO.OBJECT_NAME NOT LIKE 'BIN$%' -- except trash box, since Oracle10.1.0
ORDER BY 
    DP.NAME
  , DP.TYPE
  , DP.REFERENCED_OWNER
  , DP.REFERENCED_NAME
  , DP.REFERENCED_TYPE
  , DP.REFERENCED_LINK_NAME ;
SPOOL OFF
-- SYS.STANDARD は Oracle社が提供している標準関数(SQL language referenceに載っている関数)を1つ以上使っていると当リストに載る
CLEAR BREAK
COLUMN NAME                  CLEAR
COLUMN TYPE                  CLEAR
COLUMN REFERENCED_NAME       CLEAR
COLUMN REFERENCED_TYPE       CLEAR
COLUMN REFERENCED_LINK_NAME  CLEAR
COLUMN DEPENDENCY_TYPE       CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET TERMOUT OFF