Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Absolute file number and relative file number Oracle
  • Handling LOB data in Oracle Oracle
  • Resolving RMAN Hung Jobs Oracle
  • Unix split command to split files Linux/Unix
  • Generating XML from SQLPLUS Oracle
  • Rownum with Order by Oracle
  • Locktree.sql Oracle
  • temp_use.sql diplays usage of temp ts Oracle
  • Find Stale DR Physical Standby Oracle
  • Database link password in user_db_links Oracle
  • My FTP Job Scheduling for www.pvmehta.com PHP/MYSQL/Wordpress
  • perf_today.sql Oracle
  • Kernel Parameters for Solaris Linux/Unix
  • Finding last recovered file on DR and remove all chanracters before any “/” Linux/Unix
  • Adding Datafile on Primary Server and Impact on Standby Server Oracle

SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql

Posted on 21-Apr-2006 By Admin No Comments on SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql

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 . This means, that you do note have permissions on the object depended on, therefore you cannot obtain it’s name.

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 */

/

Oracle, SQL scripts

Post navigation

Previous Post: DBMS_PROFILER for tuning PLSQL programs.
Next Post: Find Multiple levels of object dependencies : depen.sql

Related Posts

  • Rman Notes -1 Oracle
  • sess_server.sql Oracle
  • CTAS with LONG Column for 7.x and 8 and 8i Oracle
  • On solaris 10, “S” link is not part of $ORACLE_HOME/bin/oracle as default. ( For 9.2.0.8) Oracle
  • oracle Dba site Oracle
  • Oracle Internal Good Websites 1 Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Follwoing korn shell is used to move files from source folder to destination folder and gzip it in destination folder. mv_iotto.ksh Linux/Unix
  • load SPM baseline from cursor cache Oracle
  • Benefits and Usage of RMAN with Standby Databases Oracle
  • Find_stale_dr.sql finding stale physical DR.. Oracle
  • Find_table_size.sql Oracle
  • Find total file sizes Linux/Unix
  • on IBM-AIX for display Linux/Unix
  • tab.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme