• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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.
0
OCUBE
Asked:
OCUBE
  • 2
3 Solutions
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now