How to find all Referenced Oracle Objects from Head to Tale

Posted on 2007-08-06
Last Modified: 2013-12-19
Could anyone share with me a way -- preferably a script which could help me to get the below information.

Say i have a Procedure or package or anyother stored subprogram(Also apart from stored programs views and materialized views),i need a script say to which i can pass this Procedure/Package/View etc name and then the code should give me the data results as which all other stored subprograms are used within the particular stored program name which we mentioned in our WHERE or Predicate clause.

Question by:suhinrasheed
    LVL 12

    Accepted Solution

    this script shows dependencies (used by) for package MY_PACKAGE owned by user SCOTT:

    SELECT owner
    FROM   SYS.all_objects
    WHERE  object_id IN (
                       SELECT     object_id
                       FROM       public_dependency
                       CONNECT BY PRIOR object_id = referenced_object_id
                       START WITH referenced_object_id =
                                                        (SELECT object_id
                                                         FROM   SYS.all_objects
                                                         WHERE  owner = 'SCOTT' AND
                                                                object_name = 'MY_PACKAGE' AND
                                                                object_type = 'PACKAGE'));

    this script shows dependencies (uses) for package MY_PACKAGE owned by user SCOTT:

    SELECT     object_id
    FROM       public_dependency
    START WITH object_id = (SELECT object_id
                            FROM   SYS.all_objects
                            WHERE  owner = 'SCOTT' AND
                                   object_name = 'MY_PACKAGE' AND
                                   object_type = 'PACKAGE')
    CONNECT BY PRIOR referenced_object_id = object_id;

    is this what you're looking for?
    LVL 28

    Expert Comment

    select *
    from user_dependencies
    where name ='MYPACKAGE'

    You can run the above in toad to see all the dependent objects. NOTE: This cannot show all the objects used in dynamic sql. Only objects which are referenced in static sql are shown.
    LVL 21

    Expert Comment

     select * from sys.ALL_SOURCE
    LVL 21

    Expert Comment

    Sorry,I mean   select * from sys.ALL_SOURCE
      WHERE  owner = 'SCOTT' AND
                                   object_name = 'MY_PACKAGE' AND
                                   object_type = 'PACKAGE'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    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…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now