Which objects depend on a given table?

November 10, 2009

in Data Dictionary, Dependencies

Database Object Dependencies

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 for you. 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.

{ 2 comments… read them below or add one }

Yuri April 1, 2014 at 12:20 pm

Hi,
Views *_dependencies give an answer about dependency, but it’s important to know how a table used (select, insert, update, delete,% rowtype or % type).
I would suggest you try my program ODA – Oracle object Dependencies Analyzer and I will be interested in your opinion about it.
My site’s http://www.samtrest.com

Jacob February 4, 2015 at 8:47 pm

Very helpful! Thanks.

Leave a Comment

Previous post:

Next post: