Oracle dbms_utility.get_dependency to Get Dependent Objects or Dependencies of a Table

advertisements

_____________________________________________________________________________________________________________________

It is a common requirement for a DBA or developer to find out the dependent objects or dependencies for a table. You can use dbms_utility .get_dependencypackage for this purpose.

Syntax:

exec dbms_utility.get_dependency  ('TABLE', 'TABLEOWNER', 'TABLENAME');

See some examples here.

SQL> SET SERVEROUTPUT ON

SQL> CREATE SYNONYM SYN_EMP FOR EMP;

Synonym created.

SQL> exec dbms_utility.get_dependency('TABLE','STHOMAS','EMP');

-

DEPENDENCIES ON STHOMAS.EMP

------------------------------------------------------------------

*TABLE STHOMAS.EMP()

*   SYNONYM STHOMAS.SYN_EMP()

PL/SQL procedure successfully completed.

Check after creating a trigger on the table

SQL> CREATE TRIGGER TR_EMP AFTER UPDATE ON EMP

BEGIN

  NULL;

END;

/

Trigger created.

SQL>  exec dbms_utility.get_dependency('TABLE','STHOMAS','EMP');

-

DEPENDENCIES ON STHOMAS.EMP

------------------------------------------------------------------

*TABLE STHOMAS.EMP()

*   SYNONYM STHOMAS.SYN_EMP()

*   TRIGGER STHOMAS.TR_EMP()

PL/SQL procedure successfully completed.

Constraints are not included in this.

SQL> alter table dept add constraint pk_dept primary key (deptno);

Table altered.

SQL> alter table emp add constraint fk_emp_dept foreign key (deptno)

references dept(deptno);  2

Table altered.

SQL> exec dbms_utility.get_dependency('TABLE','STHOMAS','EMP');

-

DEPENDENCIES ON STHOMAS.EMP

------------------------------------------------------------------

*TABLE STHOMAS.EMP()

*   SYNONYM STHOMAS.SYN_EMP()

*   TRIGGER STHOMAS.TR_EMP()

PL/SQL procedure successfully completed.

Check after creating a dependent procedure.

CREATE OR REPLACE PROCEDURE PR_EMP AS

X NUMBER ;

BEGIN

SELECT 1 INTO X FROM EMP WHERE ROWNUM<2 span="">

END;

SQL> exec dbms_utility.get_dependency('TABLE','STHOMAS','EMP');

-

DEPENDENCIES ON STHOMAS.EMP

------------------------------------------------------------------

*TABLE STHOMAS.EMP()

*   SYNONYM STHOMAS.SYN_EMP()

*   TRIGGER STHOMAS.TR_EMP()

*   PROCEDURE STHOMAS.PR_EMP()

PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________

Website Stats