Oracle: DBA_DEPENDENCIES

The *_dependencies comes in all three variations (dba_dependencies, all_dependencies, user_dependencies) and shows dependencies between objects in the database.

Simple example

create table tq84_root (a number);

create view tq84_dep_1 as select a*2  a2 from tq84_root;

create view tq84_dep_2 as select a2*2 a4 from tq84_dep_1;

select name from user_dependencies where referenced_name = 'TQ84_ROOT';
-- TQ84_DEP_1

select name from user_dependencies where referenced_name = 'TQ84_DEP_1';
-- TQ84_DEP_2

drop view  tq84_dep_2;
drop view  tq84_dep_1;
drop table tq84_root purge;

Github repository Oracle-Patterns, path: /Installed/data-dictionary/dependencies/example-01.sql

Recursively querying dependencies

The *_dependencies views only return the immediate dependencies of an object. In order to select all dependencies, a recursive select statement is required.

Objects that a given object depends on

The following recursive common table expression (CTE) queries all objects on which all_file_group_files depends.

with cur(owner, name, type, lvl) as (
  select
     obj.owner      ,
     obj.object_name,
     obj.object_type,
     0     lvl
  from
     all_objects obj
  where
     obj.object_name = 'ALL_FILE_GROUP_FILES' /* 'DBMS_DDL' */ and
     obj.object_type = 'SYNONYM'                                    union all
  select
     nxt.referenced_owner,
     nxt.referenced_name ,
     nxt.referenced_type ,
     cur.lvl +1
  from
     cur                                                    join
     dba_dependencies nxt on nxt.owner = cur.owner and
                             nxt.name  = cur.name  and
                             nxt.type  = cur.type
)
search depth first by owner, name, type set order_by
select
   lpad(' ', lvl*2) || owner || '.' || name || ' (' || type || ')'
from
   cur
order by
   order_by
;
--
-- PUBLIC.ALL_FILE_GROUP_FILES (SYNONYM)
--   SYS.ALL_FILE_GROUP_FILES (VIEW)
--     SYS._ALL_FILE_GROUP_FILES (VIEW)
--       SYS.FGR$_FILE_GROUP_FILES (TABLE)
--       SYS.FGR$_FILE_GROUP_VERSIONS (TABLE)
--       SYS._ALL_FILE_GROUPS (VIEW)
--         SYS.FGR$_FILE_GROUPS (TABLE)
--         SYS.OBJ$ (TABLE)
--         SYS.OBJAUTH$ (TABLE)
--         SYS.USER$ (TABLE)
--         SYS.V$ENABLEDPRIVS (VIEW)
--         SYS.X$KZSRO (TABLE)

Github repository Oracle-Patterns, path: /Installed/data-dictionary/dependencies/recursive.sql

Objects that are dependent on a given object

The following SQL statement goes the other route. It recursively shows which objects are dependent on a given object (here: x$kglob).

with cur(owner, name, type, lvl) as (
  select
     'SYS'       owner,
     'X$KGLOB'   name ,
     'TABLE'     type ,
      0          lvl
  from
     dual
                                  union all
  select
     nxt.owner,
     nxt.name ,
     nxt.type ,
     cur.lvl +1
  from
     cur                                                            join
     dba_dependencies nxt on nxt.referenced_owner = cur.owner and
                             nxt.referenced_name  = cur.name  and
                             nxt.referenced_type  = cur.type
)
search depth first by owner, name, type set order_by
select
   lpad(' ', lvl*2) || owner || '.' || name || ' (' || type || ')' as x
from
   cur
order by
   order_by
;

Cleaning a schema

The following script tries to clean a schema by repeatedly iterating over the objects on which no other object is depending and then dropping the respective objects. The script terminates when it does not find any object anymore.

I once needed this script in a project where I as not allowed to drop … cascade a schema.

declare
   objects_available boolean;
begin

   if user != 'RENE' then -- {
      dbms_output.put_line('Script must be run as RENE');
      return;
   end if; -- }

   if sys_context('userenv', 'db_name') != 'THEDB' then -- {
      dbms_output.put_line('Script must be run on database THEDB');
      return;
   end if; -- }

   loop -- {
      objects_available := false;

      for r in ( -- {
        select
            obj.object_name,
            obj.object_type
         from
            user_objects     obj
         where
              obj.object_type not in ('INDEX', 'LOB', 'PACKAGE_BODY') and
         not (obj.object_type = 'SEQUENCE' and obj.generated = 'Y') -- Prevent ORA-32794: cannot drop a system-generated sequence
       minus
         select
            referenced_name       ,
            referenced_type
        from
            user_dependencies
        where
         not
         (
            (referenced_type = 'PACKAGE'      and referenced_owner = 'SYS' and referenced_name = 'STANDARD') or
            (referenced_type = 'PACKAGE BODY' and referenced_owner = 'SYS' and referenced_name = 'STANDARD')
         )
      )
      loop
          dbms_output.put_line('drop ' || r.object_type || ' ' || r.object_name || case when r.object_type = 'TABLE' then ' purge' end);
          objects_available := true;

          begin
             execute immediate    'drop ' || r.object_type || ' ' || r.object_name || case when r.object_type = 'TABLE' then ' purge' end ;
          exception when others then
             dbms_output.put_line(sqlerrm);
          end;

      end loop; -- }

      exit when not objects_available;

   end loop; -- }

end;
/

Github repository Oracle-Patterns, path: /Installed/data-dictionary/dependencies/clean-schema.sql