How to Check / Find the Oracle Table Depended Objects?

There are two ways to find out the depended objects in Oracle.
First method you can use user_dependencies/ dba_dependencies view
Secondly, you can use deptree table to find out the details.

  1. Using User_dependencies/DBA_dependencies


SQL> CREATE VIEW V_EMP AS SELECT EMP_NAME FROM EMP;

View created.

SQL> CREATE SYNONYM SYN_EMP FOR EMP;

Synonym created.

SQL>
create or replace procedure pr_sal is
cursor c1 is select emp_name from emp;
begin
   for rec1 in c1 loop
       dbms_output.put_line(rec1.emp_name);
   end loop;
end;
/

Procedure created.

SQL> select name, TYPE, REFERENCED_NAME, REFERENCED_TYPE
     from   user_dependencies where REFERENCED_NAME='EMP';

NAME       TYPE               REFERENCED REFERENCED_TYPE
---------- ------------------ ---------- ------------------
PR_SAL     PROCEDURE          EMP        TABLE
SYN_EMP    SYNONYM            EMP        TABLE
V_EMP      VIEW               EMP        TABLE

  1. Using deptree table
For this you have to run /rdbms/admin/utldtree.sql package to create required table and views

  1. SQL> @?/rdbms/admin/utldtree.sql
  2. Load the required tables information to the deptree table.
SQL> exec deptree_fill(type =>'TABLE',schema => 'SCOTT',name => 'EMP');
  1. Now you can select the dependency data from deptree table. NESTED_LEVEL column gives the details the object is directly depended on the main object. Here in below example SYNO_EMP object is a synonym for SYN_EMP. That is why the nested_level shows 2 for this object.

SQL> select * from deptree order by seq#;

NESTED_LEVEL TYPE                SCHEMA                         NAME             SEQ#
------------ ------------------- ------------------------------ ---------- ----------
           0 TABLE               SCOTT                        EMP                 0
           1 SYNONYM             SCOTT                        SYN_EMP             1
           2 SYNONYM             SCOTT                        SYNO_EMP            2
           1 VIEW                SCOTT                        V_EMP               3
           1 PROCEDURE           SCOTT                        PR_SAL              4
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

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