Which objects depend on a given table?

A database table usually has other objects referring to it – tables linked through foreign keys, stored procedures referring to it.

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?

The table ALL_DEPENDENCIES has the answer to the question: Which objects refer to this table? Walk through the following examples for more.

Some background information first.

ALL_DEPENDENCIES

The table 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.

Example 1

Creating a package referring to the SCOTT.EMP table, and verifying that it’s listed by the dependency search query.

SQL> -- Query before any dependent package exists
SQL> select name
2               , type
3               , referenced_name
4               , referenced_type
5   from all_dependencies
6   where referenced_name like 'EMP'
7   and     referenced_owner = 'SCOTT';

no rows selected

SQL> -- Create test package
SQL> create or replace package pkg_test
2   is
3       procedure prc_emp;
4   end;
5   /

Package created.

SQL> -- Create test package body
SQL> create or replace package body pkg_test
2   is
3       -- Procedure which refers to SCOTT.EMP
4       procedure prc_emp
5       is
6           v_empno emp.empno%type;
7       begin
8           select empno
9           into     v_empno
 10           from     emp
 11           where   rownum = 1;
 12       end prc_emp;
 13
 14   end;
 15   /

Package body created.

SQL> -- Query after dependent package is created
SQL> select name
2               , type
3               , referenced_name
4               , referenced_type
5   from all_dependencies
6   where referenced_name like 'EMP'
7   and     referenced_owner = 'SCOTT';

NAME                                 TYPE                       REFERENCED_NAME           REFERENCED_TYPE
-------------------- --------------- -------------------- ---------------
PKG_TEST                         PACKAGE BODY       EMP                                   TABLE

Example 2

Remember the trigger we had created on table t_autoinc, to implement auto-increment functionality? Let’s see if the trigger is listed as a dependency.

SQL> select name
2               , type
3               , referenced_name
4               , referenced_type
5   from all_dependencies
6   where referenced_name like 'T_AUTOINC';

NAME                                 TYPE                       REFERENCED_NAME           REFERENCED_TYPE
-------------------- --------------- -------------------- ---------------
TRG_AUTOINC                   TRIGGER                 T_AUTOINC                       TABLE

There it is.

Summary

With Oracle, finding out direct dependencies between objects is a simple task. Just a query on the *DEPENDENCIES table/views will do it for you.

Oracle has a great way of resolving issues due to dependencies, especially of the cascading kind. We’ll look at this closely in the coming articles.