아래의 스크립트는 주어진 Object의 Dependency Tree를 보여 줍니다.
(예를들어 테이블 이라면 외래키로 그 테이블을 참조하고 있는 것들을 보여줍니다.)
=========================================================================
set arraysize 1
undefine ObjName
accept ObjName prompt "Object Name ? "
column a heading "Object|Name" justify center format a35
column b heading "Constraint|Name" justify center format a35
select lpad(' ',(a.nivel-1)*2)||obj.name a,
lpad(' ',(a.nivel-1)*2)||cons.name b
from sys.obj$ obj,
sys.con$ cons,
(
select obj# obj#,
con#,
level nivel
from sys.cdef$
where rcon# is not null AND
robj# is not null
connect by robj# = prior obj# and
robj# != obj# and
prior robj# != prior obj#
start with robj# = (select obj#
from sys.obj$
where name = upper('&&ObjName') AND
/* For 7.3 must change the following to: */
/* type = 2 AND */
type# = 2 AND
owner# = userenv('SCHEMAID'))) a
where cons.con# = a.con# AND
obj.obj# = a.obj# AND
/* For 7.3 must change the following to: */
/* obj.type = 2 */
obj.type# = 2
UNION ALL
select lpad(' ',(a.nivel-1)*2)||obj.name a,
to_char(null)
from sys.obj$ obj,
(
select d_obj# obj#,
level nivel
from sys.dependency$
connect by p_obj# = prior d_obj#
start with p_obj# = (select obj#
from sys.obj$
where name = upper('&&ObjName')
AND
owner# = userenv('SCHEMAID'))) a
where obj.obj# = a.obj#
AND
/* For 7.3 must change the following to: */
/* obj.type != 2 */
obj.type# != 2
/
==============
Sample Output:
==============
SQL> @savedscript
Object Name ? emp
old 17: where name = upper('&&ObjName') AND
new 17: where name = upper('emp') AND
old 34: where name = upper('&&ObjName')
new 34: where name = upper('emp')
Object Constraint
Name Name
----------------------------------- -----------------------------------
OLCAY_TEST
| |
