ORCL - Dependency

We have a table, and we want to find all procedures/objects do DML on this table.

If you use any IDE such as PL/SQL Developer, you will easily find any object which has anything to do with this table by checking Referenced by.

Oracle finds the object dependents with some internal tables. You can find the dependencies via selecting user_dependencies view or executing dbms_utility.get_dependency procedure.

Querying user_dependencies view:
SQL> select * from user_dependencies where referenced_name = 'ALARM_REMINDER_LOG';

Executing dbms_utility.get_dependency:
SQL> exec dbms_utility.get_dependency( 'TABLE', 'advance', 'pledge_reminder_log');

PL/SQL procedure successfully completed

Everything is from Oracle Data Dictionary? Right! Objects may have some dependencies on theirselves. When creating a package or a trigger you may need other objects. When compiling an object or executing an alter command, you make dependent objects invalid. On calling objects firstly Oracle check object status. If object s valid tehere is no problem. But if an object is invalid, Oracle tries to compile the dependent objects. If there is no problem, statement executed and object status will become valid.