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

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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google