Subject: Script To List Recursive Dependency Between Objects
Doc ID: Note:139594.1 Type: BULLETIN
Last Revision Date: 16-JUN-2004 Status: PUBLISHED
PURPOSE
——-
This script provides a listing of how objects depend on other given objects such as packages, package bodies or procedures recursively.
SCOPE & APPLICATION
——————-
You often get invalidated objects and would like to know the recursive dependency of these objects to other objects.
SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS
—————————————————
Oracle provides script utldtree.sql in $ORACLE_HOMErdbmsadmin, which recursively lists objects, that depend on a given object. However this
script, lists reverse information – objects, on which other objects depend on.
The script in this document is created from utldtree.sql and modified to display reverse dependencies. The usage is described in the script body.
Here is simple example of how to use the script:
First connect as the user under which you want to analyze the dependencies:
SQL> connect test/test
The first time you execute this script it will create procedures, views and tables that will be used:
SQL> @rdeptree.sql
Then, you must call procedure rdeptree_fill, which will populate information about the objects which other objects depend on into temporary tables.
The procedure rdeptree_fill has three parameters:
type – type of analyzing object (same as object_type in dba_objects view)
schema – schema in which analyzing object resides
name – name of analyzing object
The next call will store informations about PROCEDURE TEST.P1:
SQL> exec rdeptree_fill(‘PROCEDURE’, ‘TEST’, ‘P1’);
PL/SQL procedure successfully completed.
Now you will see the dependencies using the following select:
SQL> select * from irdeptree;
DEPENDENCIES
——————————————————————————–
PROCEDURE TEST.P1
PACKAGE SYS.STANDARD
VIEW TEST.V1
TABLE TEST.T1
PACKAGE SYS.STANDARD
PACKAGE SYS.SYS_STUB_FOR_PURITY_ANALYSIS
6 rows selected.
You can see all the objects on which PROCEDURE TEST.P1 depends. For Example, TEST.P1 depends on VIEW TEST.V1. This view then depends on TEST.T1 TABLE. Invalidating of these objects will cause invalidating of TEST.P1 procedure. You may also see in this output line
For analyzing another object do not delete any data from the tables, just run the rdeptree_fill procedure again.
RELATED DOCUMENTS
—————–
Script utldtree.sql in $ORACLE_HOMErdbmsadmin
Here is this script
=================================================================================
Rem
Rem NAME
Rem rdeptree.sql – Show objects which a given object is recursively
Rem dependent on
Rem DESCRIPTION
Rem This procedure, view and temp table will allow you to see all
Rem objects that a given object recursively depends on.
Rem Note: you will only see objects for which you have permission.
Rem Examples:
Rem execute rdeptree_fill(‘procedure’, ‘scott’, ‘billing’);
Rem select * from rdeptree order by seq#;
Rem
Rem execute rdeptree_fill(‘table’, ‘scott’, ’emp’);
Rem select * from rdeptree order by seq#;
Rem
Rem execute rdeptree_fill(‘package body’, ‘scott’, ‘accts_payable’);
Rem select * from rdeptree order by seq#;
Rem
Rem A better way to display this information than:
Rem select * from rdeptree order by seq#;
Rem is
Rem select * from irdeptree;
Rem
Rem This shows the dependency relationship via indenting. Notice
Rem that no order by clause is needed with ideptree.
Rem RETURNS
Rem
Rem NOTES
Rem Run this script once for each schema that needs this utility.
drop sequence rdeptree_seq
/
create sequence rdeptree_seq cache 200 /* cache 200 to make sequence faster */
/
drop table rdeptree_temptab
/
create table rdeptree_temptab
(
object_id number,
referenced_object_id number,
nest_level number,
seq# number
)
/
create or replace procedure rdeptree_fill (type char, schema char, name char) is
obj_id number;
begin
delete from rdeptree_temptab;
commit;
select object_id into obj_id from all_objects
where owner = upper(rdeptree_fill.schema)
and object_name = upper(rdeptree_fill.name)
and object_type = upper(rdeptree_fill.type);
insert into rdeptree_temptab
values(0, obj_id, 0, 0);
insert into rdeptree_temptab
select object_id, referenced_object_id,
level, rdeptree_seq.nextval
from public_dependency
connect by object_id = prior referenced_object_id
start with object_id = rdeptree_fill.obj_id;
exception
when no_data_found then
raise_application_error(-20000, ‘ORU-10013: ‘ ||
type || ‘ ‘ || schema || ‘.’ || name || ‘ was not found.’);
end;
/
drop view rdeptree
/
set echo on
set echo off
create view rdeptree
(nested_level, type, schema, name, seq#)
as
select d.nest_level, o.object_type, o.owner, o.object_name, d.seq#
from rdeptree_temptab d, all_objects o
where d.referenced_object_id = o.object_id (+)
/
drop view irdeptree
/
create view irdeptree (dependencies)
as
select lpad(‘ ‘,3*(max(nested_level))) || max(nvl(type, ‘
|| ‘ ‘ || schema || decode(type, NULL, ”, ‘.’) || name)
from rdeptree
group by seq# /* So user can omit sort-by when selecting from ideptree */
/