Oracle SYS tables question?


 I am looking for the following info from our database:

 Please point to the sys tables or views which can give me this info:

1) We need to find all objects that use a certain function/procedure, or view etc.

     Example:-  If I give a function name "XYZ" , the sys table needs to show me what all
                      objects are being used in that function.

     Example:-  If I give a table name "ABC"  , the sys table needs to show me where all
                      this object is being used ( function,view,procedure)

2)  If I give a column_name it needs to show me where all this similar column_name is being
     used in my entire database objects ?


 Give me the SYS view names with query if possible to find the answers for the above questions.

 Any commercial tool is also good -if there is one to make our life easier.
OCUBEAsked:
Who is Participating?
 
sdstuberCommented:
to find all tables that have certain column name look in  dba_tab_cols

to find all objects that reference another object (your function) look in dba_dependencies
ee.txt
0
 
johnsoneSenior Oracle DBACommented:
Keep in mind anything that uses dynamic SQL (particularly EXECUTE IMMEDIATE) will not be listed in DBA_DEPENDENCIES as this is a run time dependency and not a compile time dependency.  Only searching through the code (use DBA_SOURCE) will find these type of references.
0
 
sdstuberCommented:
any code external to the database won't use it either.

nor will any java stored procedures with embedded sql

nor anything that uses dbms_sql to execute dynamic sql
0
 
OCUBEAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.