[Book Script] 오브젝트 Dependancy 확인 하는 쿼리

haisins

7년 ago

PROMPT =============================
PROMPT ====== DEPENDENCY CHECK =====
PROMPT =============================
SELECT decode(name, UPPER('&&object_name'), 'REF', 'REF BY') ref, d.*
  FROM dba_dependencies d
 WHERE name=UPPER('&&object_name') OR referenced_name=UPPER('&&object_name')
 ORDER BY ref, owner, type, name, referenced_owner, referenced_type, referenced_name
/


PROMPT =======================================
PROMPT ====== DEPENDENCY STATUS CHECK =====
PROMPT =======================================
SELECT owner, object_name, object_type, status
  FROM dba_objects
 WHERE (owner, object_name) IN ( SELECT referenced_owner, referenced_name
                                   FROM dba_dependencies d
                                  WHERE name=UPPER('&&object_name')
                                  UNION ALL
                                 SELECT owner, name
                                   FROM dba_dependencies d
                                  WHERE referenced_name=UPPER('&&object_name') )
 ORDER BY owner, status desc, object_type desc, object_name
/

PROMPT =============================

PROMPT ====== DEPENDENCY CHECK =====

PROMPT =============================

SELECT decode(name, UPPER('&&object_name'), 'REF', 'REF BY') ref, d.*

FROM dba_dependencies d

WHERE name=UPPER('&&object_name') OR referenced_name=UPPER('&&object_name')

ORDER BY ref, owner, type, name, referenced_owner, referenced_type, referenced_name

PROMPT =======================================

PROMPT ====== DEPENDENCY STATUS CHECK =====

PROMPT =======================================

SELECT owner, object_name, object_type, status

FROM dba_objects

WHERE (owner, object_name) IN ( SELECT referenced_owner, referenced_name

FROM dba_dependencies d

WHERE name=UPPER('&&object_name')

UNION ALL

SELECT owner, name

FROM dba_dependencies d

WHERE referenced_name=UPPER('&&object_name') )

ORDER BY owner, status desc, object_type desc, object_name