Andrew Fraser DBA

Select out all code refererencing some tables

This allows you to get code listings for all code (procedures, views, materialized views, etc.) that reference particular tables:

set long 200000 pages 0 verify off lines 131 feed off
column txt format a121 word_wrapped
column spoolfile new_value spoolfile noprint
spool go.tmp
select '@2 '|| decode(type , 'MATERIALIZED VIEW' , 'MATERIALIZED_VIEW' , type)  ||' '||name||' '||owner
from dba_dependencies where referenced_name in ('TABLE1','TABLE2','TABLE3')
order by 1 ;
spool off
@go.tmp

That calls files 2.sql which contains:

select lower( '&1' ||'.'|| '&2' || '.sql' ) spoolfile from dual ;
spool &spoolfile
select dbms_metadata.get_ddl('&1','&2','&3') txt from dual;
spool off