| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: script to show heirarchical list of object dependencies for a given
> create table depends as select * from
> sys.dba_dependencies;
Depends? Like the adult diaper?
Doing a little CYA?
Jared
Jack Silvey <jack_silvey_at_yahoo.com>
Sent by: root_at_fatcity.com
05/09/2002 04:03 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: script to show heirarchical list of object dependencies for a given object
Greetings listers,
Ever worry about wrinkles, loss of hair, and bad breath?
Well, the following script can't help you with THAT, but it CAN show you a heirarchical list of objects that depend upon the given object! Just pass in &1=owner and &2=object name, and viola! No more changing objects without knowing what depends on them (and might break). (The reason for the temp table is that you can't do recursive self-join sql on complex views.) This can be a very useful script if you make production database changes.
prompt
prompt objects depending on &1. &2:
prompt
set termout off
set head off
set verify off
drop table depends;
create table depends as select * from
sys.dba_dependencies;
set termout on
select '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('&1')
and referenced_name = upper('&2')
and owner is not null
/
good luck,
jack silvey
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: jack_silvey_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu May 09 2002 - 19:53:51 CDT
 |
 |