Oracle – Listing Objects Using Another Object

oracle

I have to move certain objects from a schema to another. The database is rather large and
I want to make sure we test properly all affected objects.

On TOAD there is a Tab called "Used By" that show all objects that are referencing the selected object.

Is there a way to achieve a similar output with an SQL Command?
I tried checking the different DBA Views but I haven't been able to find any with the information I need.

Best Answer

This seems to be the query from TOAD when you click the used_by tab - you just need to replace the 'OBJECT_NAME' with whatever the name of the object you are interested in is, and change the object_type to whatever is appropriate for your object. 

WITH Objs
     AS (    SELECT object_id
               FROM (SELECT object_id, referenced_object_id
                       FROM public_dependency
                       WHERE referenced_object_id <> object_id) pd
         CONNECT BY PRIOR object_id = referenced_object_id
         START WITH referenced_object_id = (select object_id from dba_objects where object_name = 'OBJECT_NAME' and object_type = 'TABLE'))
SELECT o.owner,
       o.object_type,
       o.object_name,
       o.object_id,
       o.status
  FROM sys.DBA_OBJECTS o, Objs
 WHERE o.Object_id = Objs.Object_id

You can view the SQL that TOAD generates by starting a second session, go into the session browser and then view the current statement to see the query your first TOAD session is running.

Related Question