How to find all Referenced Oracle Objects from Head to Tale

Hi,
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.

Regards,
Suhin
suhinrasheedAsked:
Who is Participating?
 
jwahlCommented:
this script shows dependencies (used by) for package MY_PACKAGE owned by user SCOTT:

SELECT owner
      ,object_type
      ,object_name
      ,object_id
      ,status
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
          ,referenced_object_id
          ,LEVEL
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?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
select *
from user_dependencies
where name ='MYPACKAGE'
AND OWNER ='SCOTT';

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.
0
 
oleggoldCommented:
 select * from sys.ALL_SOURCE
0
 
oleggoldCommented:
Sorry,I mean   select * from sys.ALL_SOURCE
  WHERE  owner = 'SCOTT' AND
                               object_name = 'MY_PACKAGE' AND
                               object_type = 'PACKAGE'
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.