Print Object Dependencies Recursively

 OCPdba.Net

  • Using the procedure below, you can examine the database to provide a listing of objects dependent on the supplied object.
  • This is recursive, so you get a tree of dependencies as your output.
  • Note that you should create it as SYS with a publicly executable synonym. 
    •    
   --    Author:              Ahbaid Gaffoor - OCPdba.Net
   --    Date:                Sunday 18th August 2002
   --    File:                proc_show_dependencies.sql
   --    Purpose:             This procedure recursively examines DBA_DEPENDENCIES 
   -- 			and prints a dependency tree for an object. 
   -- 
   --    Usage:               exec show_dependencies('..');
   -- 
   --    Example:
   --                         set serveroutput on
   --                         begin
   --                            show_dependencies('SCOTT.EMPLOYEE.TABLE');
   --                         end;
   --                         /
   -- 
   --    Notes:               Create as SYS and create a public synonym for the procedure
   
   create or replace procedure show_dependencies(objectstr varchar2, plevel number := 0) as
   
      fowner varchar2(30);
      fname varchar2(30);
      ftype varchar2(30);
   
      cursor c1 (f1 varchar2, f2 varchar2, f3 varchar2) is 
                   select owner, name, type
                   from dba_dependencies
                   where referenced_owner = f1 and
                         referenced_name = f2 and
                         referenced_type = f3;
   
      c1var c1%ROWTYPE;
   
   begin
   
      fowner := substr(objectstr,1,instr(objectstr,'.',1,1)-1);
      fname  := substr(objectstr,instr(objectstr,'.',1,1)+1,instr(objectstr,'.',1,2)-instr(objectstr,'.',1,1)-1);
      ftype  := substr(objectstr,instr(objectstr,'.',1,2)+1);
   
   
      open c1(fowner,fname,ftype);
      loop
         fetch c1 into c1var;
         exit when c1%NOTFOUND;
   
            dbms_output.put_line(lpad('=',plevel+2,'=')||c1var.owner||'.'||c1var.name||'-'||c1var.type);
            show_dependencies(c1var.owner||'.'||c1var.name||'.'||c1var.type,plevel+1);
   
      end loop;
      close c1;
   
   end;
   /
   show errors
   
   grant execute on show_dependencies to public;
   create public synonym show_dependencies for show_dependencies;

  • You may also use the following wrapper script to call the procedure more easily from SQL. 
    • 
   --    Author:	Ahbaid Gaffoor - OCPdba.Net
   --    Date:		Sunday 18th August 2002
   --    File:		showdeps.sql
   --    Purpose:	SQL wrapper script for show_dependencies procedure
   --    Usage:		@showdeps OWNER OBJECT_NAME OBJECT_TYPE
   --    Example:	@showdeps SCOTT EMPLOYEE TABLE
   
   set serveroutput on size 400000
   set lines 130
   set pages 0
   set verify off
   begin
     show_dependencies(upper('&&1')||'.'||upper('&&2')||'.'||upper('&&3'));
   end;
   /
   set verify on
    OCPdba.Net