Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4201
  • Last Modified:

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
0
suhinrasheed
Asked:
suhinrasheed
  • 2
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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