How to know if a table is used by any object

soft_techy
soft_techy used Ask the Experts™
on
I am writing a script to clean up some unwanted tables in mu database. I grabbed the names of tables using regex. But before i drop them i want to make sure they are not used by any procedure or function in my script. Is there a way to do it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select * from dba_dependencies where referenced_owner  ='YOUR_OWNER'
and referenced_name= 'YOUR_TABLE'
and referenced_type = 'TABLE'
Most Valuable Expert 2011
Top Expert 2012
Commented:
dba_dependencies will let you search through all objects on the database that might reference your table

it will not however, let you search through procedure defined within an anonymous block in your script you will have to grep through those by some other means
David VanZandtOracle Database Administrator III

Commented:
Remember that 10g forward, Oracle provides a recycle bin to keep dropped tables safe until the bin is purged.  This does not address your speciifc question but rather serves a buffer zone for your whoops.
 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial