0

I want to find all the objects referencing my object.

I came up with this query, Is this the best way to get all references to an object? (I know this doesn't include constraints, I handle that separately)

Direct References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS  
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name

Also, on the internet, I have come across queries like the following.

Direct and Indirect References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS  
       WHERE object_id IN (SELECT object_id
                           FROM public_dependency 
                           CONNECT BY PRIOR object_id = referenced_object_id
                           START WITH referenced_object_id = :objectId)

However, if my understanding is correct, this query doesn't return direct references only, but also indirect dependencies.

For example if I want the references for object_A. If object_B references it, and object_C referenced object_B. This query would return object_B and object_C as references to object_A. Am I right?

0

After talking with some people with more Experience in Oracle than me, they told me I was right in my understanding.

This query returns the references to an object (except the constraints)

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS  
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.