Find the Object Dependencies

When we are going to change any table design then You might want to find out –

• which packages refer to this table? 
• Are there views created on it? 
• If I change the table design, how many and which objects will be affected?

Above information we will get it from ALL_DEPENDENCIES view. The view ALL_DEPENDENCIES stores information about dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user.

There are two related views:

DBA_DEPENDENCIES: describes all dependencies between objects in the database. This view may only be accessible to users with DBA rights.

USER_DEPENDENCIES: describes dependencies between objects only in the current user’s schema. This view does not have the column OWNER, as it is contained in the view definition.

 Check the below example:

SELECT name, referenced_owner, type, referenced_name, referenced_type
  FROM all_dependencies
 WHERE referenced_name = 'EMP';