Oracle SYS tables question?

Posted on 2011-10-10
Last Modified: 2012-05-12

 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.
Question by:OCUBE
    LVL 73

    Accepted Solution

    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
    LVL 34

    Assisted Solution

    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.
    LVL 73

    Assisted Solution

    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

    Author Closing Comment


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to take different types of Oracle backups using RMAN.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now