RE: Dependent objects

From: Glenn Travis <c-glenn.travis_at_wcom.com>
Date: Thu, 05 Apr 2001 13:42:11 -0700
Message-ID: <F001.002E3300.20010405132739@fatcity.com>

Here's a good script I use;

---

rem user_dep_obj.sql
rem Object Dependency Listing

set numwidth 3
set wrap on
set verify off
set recsep off
set feedback off
set space 2
set newpage 0
set pagesize 58
set linesize 80
set tab off

accept object_name prompt "Enter the name of the Object: "

break on today
column today new_value _DATE
select to_char(sysdate, 'mm/dd/yy') today from dual;

clear breaks

ttitle left '&&object_name..dependencies.lis ' right 'Printed: ' _DATE skip 1 -

       center 'Dependencies Listing' skip 1 -
        center 'For Object: &&object_name' skip 2;

btitle skip 2 center 'Page ' SQL.PNO

break on r_name skip 1

column r_name             format a80
column name               format a80
column r_link             format a80

set heading off

spool &&object_name..dependencies.lis

select decode(referenced_type, 'NON-EXISTENT', '.....', referenced_type)

         || ' ' || referenced_owner || '.' || referenced_name r_name,
         '    is referenced by: ' || type || ' ' || name name,
         '    Referenced Link: ' || decode(referenced_link_name, null,
'none',
         referenced_link_name) r_link
from     user_dependencies

where referenced_name like upper('&&object_name%') order by 1,2;

spool off
prompt
prompt
prompt * Output in &&object_name..dependencies.lis * prompt
prompt
quit

---

-----Original Message-----
Sent: Thursday, April 05, 2001 4:46 PM
To: Multiple recipients of list ORACLE-L

Hi -
Does anyone have (or know where I can find) a script to identify dependencies on a table? When we make table modifications, we would like to be able to run a script that recursively identifies dependent objects. We pulled one from Metalink, but it doesn't seem to work. Thanks -
Lisa

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Glenn Travis
  INET: c-glenn.travis_at_wcom.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 Apr 05 2001 - 15:42:11 CDT