Todas las dependencias

Buenos dias, les paso un script con el que pueden obtener la dependencia entre objetos en la db y asi cómo dependen uno de otros. Espero que les sea útil.

DECLARE
v_type        VARCHAR2(30) := 'FUNCTION';
v_object_name VARCHAR2(30);
BEGIN
dbms_output.put_line(' ' || ',' || ' ' || ',,,,,,,,,,' || 'SELECT' || ',' ||
   'INSERT' || ',' || 'UPDATE' || ',' || 'DELETE' || ',' ||
   'EXECUTE');
FOR level0 IN (SELECT obje.owner,
 obje.object_name,
 obje.object_type,
 (CASE
   WHEN obje.object_type IN ('VIEW', 'TABLE') THEN
    'X'
 END) AS vselect,
 decode(obje.object_type, 'TABLE', ' ', ' ') AS vinsert,
 decode(obje.object_type, 'TABLE', ' ', ' ') AS vupdate,
 decode(obje.object_type, 'TABLE', ' ', ' ') AS vdelete,
 (CASE
   WHEN obje.object_type IN
     ('PACKAGE',
   'PACKAGE BODY',
   'PROCEDURE',
   'FUNCTION') THEN
    'X'
 END) AS vexecute
FROM all_objects obje
WHERE obje.object_type = v_type
AND obje.owner = 'SIFICO2') LOOP
dbms_output.put_line(level0.owner || '.' || level0.object_name || ',' ||
  level0.object_type || ',,,,,,,,,,' ||
  level0.vselect || ',' || level0.vinsert || ',' ||
  level0.vupdate || ',' || level0.vdelete || ',' ||
  level0.vexecute);
FOR level1 IN (SELECT depe.referenced_owner,
   depe.referenced_name,
   depe.referenced_type,
   (CASE
  WHEN depe.referenced_type IN ('VIEW', 'TABLE') THEN
   'X'
   END) AS vselect,
   (CASE
  WHEN depe.referenced_type = 'TABLE' AND EXISTS
   (SELECT 1
    FROM dba_source sour
      WHERE sour.owner = depe.owner
     AND sour.name = depe.name
     AND sour.type = depe.type
     AND sour.text LIKE '%INSERT%INTO%'
     AND depe.referenced_name =
      fc_insert_table_usage(sour.text)) THEN
   'X'
   END) AS vinsert,
   (CASE
  WHEN depe.referenced_type = 'TABLE' AND EXISTS
   (SELECT 1
    FROM dba_source sour
      WHERE sour.owner = depe.owner
     AND sour.name = depe.name
     AND sour.type = depe.type
     AND sour.text LIKE '%UPDATE%'
     AND depe.referenced_name =
      fc_update_table_usage(sour.text)) THEN
   'X'
   END) AS vupdate,
   (CASE
  WHEN depe.referenced_type = 'TABLE' AND EXISTS
   (SELECT 1
    FROM dba_source sour
      WHERE sour.owner = depe.owner
     AND sour.name = depe.name
     AND sour.type = depe.type
     AND sour.text LIKE '%DELETE%'
     AND depe.referenced_name =
      fc_delete_table_usage(sour.text)) THEN
   'X'
   END) AS vdelete,
   (CASE
  WHEN depe.referenced_type IN
    ('PACKAGE',
     'PACKAGE BODY',
     'PROCEDURE',
     'FUNCTION') THEN
   'X'
   END) AS vexecute
 FROM all_dependencies depe
WHERE depe.name = level0.object_name
  AND depe.type = level0.object_type
  AND depe.owner NOT IN ('SIFICO', 'PUBLIC')
ORDER BY 1, 2) LOOP
dbms_output.put_line(',' || level1.referenced_owner || '.' ||
    level1.referenced_name || ',' ||
    level1.referenced_type || ',,,,,,,,,' ||
    level1.vselect || ',' || level1.vinsert || ',' ||
    level1.vupdate || ',' || level1.vdelete || ',' ||
    level1.vexecute);
FOR level2 IN (SELECT depe2.referenced_owner,
  depe2.referenced_name,
  depe2.referenced_type,
  (CASE
    WHEN depe2.referenced_type IN ('VIEW', 'TABLE') THEN
     'X'
  END) AS vselect,
  (CASE
    WHEN depe2.referenced_type = 'TABLE' AND EXISTS
     (SELECT 1
      FROM dba_source sour
     WHERE sour.owner = depe2.owner
       AND sour.name = depe2.name
       AND sour.type = depe2.type
       AND sour.text LIKE '%INSERT%INTO%'
       AND depe2.referenced_name =
        fc_insert_table_usage(sour.text)) THEN
     'X'
  END) AS vinsert,
  (CASE
    WHEN depe2.referenced_type = 'TABLE' AND EXISTS
     (SELECT 1
      FROM dba_source sour
     WHERE sour.owner = depe2.owner
       AND sour.name = depe2.name
       AND sour.type = depe2.type
       AND sour.text LIKE '%UPDATE%'
       AND depe2.referenced_name =
        fc_update_table_usage(sour.text)) THEN
     'X'
  END) AS vupdate,
  (CASE
    WHEN depe2.referenced_type = 'TABLE' AND EXISTS
     (SELECT 1
      FROM dba_source sour
     WHERE sour.owner = depe2.owner
       AND sour.name = depe2.name
       AND sour.type = depe2.type
       AND sour.text LIKE '%DELETE%'
       AND depe2.referenced_name =
        fc_delete_table_usage(sour.text)) THEN
     'X'
  END) AS vdelete,
  (CASE
    WHEN depe2.referenced_type IN
      ('PACKAGE',
    'PACKAGE BODY',
    'PROCEDURE',
    'FUNCTION') THEN
     'X'
  END) AS vexecute
   FROM all_dependencies depe2
  WHERE depe2.name = level1.referenced_name
 AND depe2.owner = level1.referenced_owner
 AND depe2.type = level1.referenced_type
 AND depe2.referenced_owner NOT IN
  ('SIFICO', 'PUBLIC')
  ORDER BY 1, 2) LOOP
dbms_output.put_line(',,' || level2.referenced_owner || '.' ||
   level2.referenced_name || ',' ||
   level2.referenced_type || ',,,,,,,,' ||
   level2.vselect || ',' || level2.vinsert || ',' ||
   level2.vupdate || ',' || level2.vdelete || ',' ||
   level2.vexecute);
FOR level3 IN (SELECT depe3.referenced_owner,
    depe3.referenced_name,
    depe3.referenced_type,
    (CASE
   WHEN depe3.referenced_type IN
     ('VIEW', 'TABLE') THEN
    'X'
    END) AS vselect,
    (CASE
   WHEN depe3.referenced_type = 'TABLE' AND
     EXISTS
    (SELECT 1
     FROM dba_source sour
       WHERE sour.owner = depe3.owner
      AND sour.name = depe3.name
      AND sour.type = depe3.type
      AND sour.text LIKE '%INSERT%INTO%'
      AND depe3.referenced_name =
       fc_insert_table_usage(sour.text)) THEN
    'X'
    END) AS vinsert,
    (CASE
   WHEN depe3.referenced_type = 'TABLE' AND
     EXISTS
    (SELECT 1
     FROM dba_source sour
       WHERE sour.owner = depe3.owner
      AND sour.name = depe3.name
      AND sour.type = depe3.type
      AND sour.text LIKE '%UPDATE%'
      AND depe3.referenced_name =
       fc_update_table_usage(sour.text)) THEN
    'X'
    END) AS vupdate,
    (CASE
   WHEN depe3.referenced_type = 'TABLE' AND
     EXISTS
    (SELECT 1
     FROM dba_source sour
       WHERE sour.owner = depe3.owner
      AND sour.name = depe3.name
      AND sour.type = depe3.type
      AND sour.text LIKE '%DELETE%'
      AND depe3.referenced_name =
       fc_delete_table_usage(sour.text)) THEN
    'X'
    END) AS vdelete,
    (CASE
   WHEN depe3.referenced_type IN
     ('PACKAGE',
      'PACKAGE BODY',
      'PROCEDURE',
      'FUNCTION') THEN
    'X'
    END) AS vexecute
  FROM all_dependencies depe3
 WHERE depe3.name = level2.referenced_name
   AND depe3.owner = level2.referenced_owner
   AND depe3.type = level2.referenced_type
   AND depe3.referenced_owner NOT IN
    ('SIFICO', 'PUBLIC')
 ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,' || level3.referenced_owner || '.' ||
     level3.referenced_name || ',' ||
     level3.referenced_type || ',,,,,,,' ||
     level3.vselect || ',' || level3.vinsert || ',' ||
     level3.vupdate || ',' || level3.vdelete || ',' ||
     level3.vexecute);
FOR level4 IN (SELECT depe4.referenced_owner,
   depe4.referenced_name,
   depe4.referenced_type,
   (CASE
     WHEN depe4.referenced_type IN
       ('VIEW', 'TABLE') THEN
      'X'
   END) AS vselect,
   (CASE
     WHEN depe4.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
      WHERE sour.owner = depe4.owner
        AND sour.name = depe4.name
        AND sour.type = depe4.type
        AND sour.text LIKE '%INSERT%INTO%'
        AND depe4.referenced_name =
         fc_insert_table_usage(sour.text)) THEN
      'X'
   END) AS vinsert,
   (CASE
     WHEN depe4.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
      WHERE sour.owner = depe4.owner
        AND sour.name = depe4.name
        AND sour.type = depe4.type
        AND sour.text LIKE '%UPDATE%'
        AND depe4.referenced_name =
         fc_update_table_usage(sour.text)) THEN
      'X'
   END) AS vupdate,
   (CASE
     WHEN depe4.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
      WHERE sour.owner = depe4.owner
        AND sour.name = depe4.name
        AND sour.type = depe4.type
        AND sour.text LIKE '%DELETE%'
        AND depe4.referenced_name =
         fc_delete_table_usage(sour.text)) THEN
      'X'
   END) AS vdelete,
   (CASE
     WHEN depe4.referenced_type IN
       ('PACKAGE',
     'PACKAGE BODY',
     'PROCEDURE',
     'FUNCTION') THEN
      'X'
   END) AS vexecute
    FROM all_dependencies depe4
   WHERE depe4.name = level3.referenced_name
  AND depe4.owner = level3.referenced_owner
  AND depe4.type = level3.referenced_type
  AND depe4.referenced_owner NOT IN
   ('SIFICO', 'PUBLIC')
   ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,' || level4.referenced_owner || '.' ||
    level4.referenced_name || ',' ||
    level4.referenced_type || ',,,,,,' ||
    level4.vselect || ',' || level4.vinsert || ',' ||
    level4.vupdate || ',' || level4.vdelete || ',' ||
    level4.vexecute);
FOR level5 IN (SELECT depe5.referenced_owner,
     depe5.referenced_name,
     depe5.referenced_type,
     (CASE
    WHEN depe5.referenced_type IN
      ('VIEW', 'TABLE') THEN
     'X'
     END) AS vselect,
     (CASE
    WHEN depe5.referenced_type = 'TABLE' AND
      EXISTS
     (SELECT 1
      FROM dba_source sour
        WHERE sour.owner = depe5.owner
       AND sour.name = depe5.name
       AND sour.type = depe5.type
       AND sour.text LIKE
        '%INSERT%INTO%'
       AND depe5.referenced_name =
        fc_insert_table_usage(sour.text)) THEN
     'X'
     END) AS vinsert,
     (CASE
    WHEN depe5.referenced_type = 'TABLE' AND
      EXISTS
     (SELECT 1
      FROM dba_source sour
        WHERE sour.owner = depe5.owner
       AND sour.name = depe5.name
       AND sour.type = depe5.type
       AND sour.text LIKE '%UPDATE%'
       AND depe5.referenced_name =
        fc_update_table_usage(sour.text)) THEN
     'X'
     END) AS vupdate,
     (CASE
    WHEN depe5.referenced_type = 'TABLE' AND
      EXISTS
     (SELECT 1
      FROM dba_source sour
        WHERE sour.owner = depe5.owner
       AND sour.name = depe5.name
       AND sour.type = depe5.type
       AND sour.text LIKE '%DELETE%'
       AND depe5.referenced_name =
        fc_delete_table_usage(sour.text)) THEN
     'X'
     END) AS vdelete,
     (CASE
    WHEN depe5.referenced_type IN
      ('PACKAGE',
       'PACKAGE BODY',
       'PROCEDURE',
       'FUNCTION') THEN
     'X'
     END) AS vexecute
   FROM all_dependencies depe5
  WHERE depe5.name = level4.referenced_name
    AND depe5.owner = level4.referenced_owner
    AND depe5.type = level4.referenced_type
    AND depe5.referenced_owner NOT IN
     ('SIFICO', 'PUBLIC')
  ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,' || level5.referenced_owner || '.' ||
      level5.referenced_name || ',' ||
      level5.referenced_type || ',,,,,' ||
      level5.vselect || ',' || level5.vinsert || ',' ||
      level5.vupdate || ',' || level5.vdelete || ',' ||
      level5.vexecute);
FOR level6 IN (SELECT depe6.referenced_owner,
    depe6.referenced_name,
    depe6.referenced_type,
    (CASE
      WHEN depe6.referenced_type IN
        ('VIEW', 'TABLE') THEN
       'X'
    END) AS vselect,
    (CASE
      WHEN depe6.referenced_type = 'TABLE' AND
        EXISTS
       (SELECT 1
        FROM dba_source sour
       WHERE sour.owner = depe6.owner
         AND sour.name = depe6.name
         AND sour.type = depe6.type
         AND sour.text LIKE
          '%INSERT%INTO%'
         AND depe6.referenced_name =
          fc_insert_table_usage(sour.text)) THEN
       'X'
    END) AS vinsert,
    (CASE
      WHEN depe6.referenced_type = 'TABLE' AND
        EXISTS
       (SELECT 1
        FROM dba_source sour
       WHERE sour.owner = depe6.owner
         AND sour.name = depe6.name
         AND sour.type = depe6.type
         AND sour.text LIKE '%UPDATE%'
         AND depe6.referenced_name =
          fc_update_table_usage(sour.text)) THEN
       'X'
    END) AS vupdate,
    (CASE
      WHEN depe6.referenced_type = 'TABLE' AND
        EXISTS
       (SELECT 1
        FROM dba_source sour
       WHERE sour.owner = depe6.owner
         AND sour.name = depe6.name
         AND sour.type = depe6.type
         AND sour.text LIKE '%DELETE%'
         AND depe6.referenced_name =
          fc_delete_table_usage(sour.text)) THEN
       'X'
    END) AS vdelete,
    (CASE
      WHEN depe6.referenced_type IN
        ('PACKAGE',
      'PACKAGE BODY',
      'PROCEDURE',
      'FUNCTION') THEN
       'X'
    END) AS vexecute
     FROM all_dependencies depe6
    WHERE depe6.name = level5.referenced_name
   AND depe6.owner = level5.referenced_owner
   AND depe6.type = level5.referenced_type
   AND depe6.referenced_owner NOT IN
    ('SIFICO', 'PUBLIC')
    ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,,' || level6.referenced_owner || '.' ||
     level6.referenced_name || ',' ||
     level6.referenced_type || ',,,,' ||
     level6.vselect || ',' ||
     level6.vinsert || ',' ||
     level6.vupdate || ',' ||
     level6.vdelete || ',' ||
     level6.vexecute);
FOR level7 IN (SELECT depe7.referenced_owner,
      depe7.referenced_name,
      depe7.referenced_type,
      (CASE
     WHEN depe7.referenced_type IN
       ('VIEW', 'TABLE') THEN
      'X'
      END) AS vselect,
      (CASE
     WHEN depe7.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
         WHERE sour.owner = depe7.owner
        AND sour.name = depe7.name
        AND sour.type = depe7.type
        AND sour.text LIKE
         '%INSERT%INTO%'
        AND depe7.referenced_name =
         fc_insert_table_usage(sour.text)) THEN
      'X'
      END) AS vinsert,
      (CASE
     WHEN depe7.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
         WHERE sour.owner = depe7.owner
        AND sour.name = depe7.name
        AND sour.type = depe7.type
        AND sour.text LIKE '%UPDATE%'
        AND depe7.referenced_name =
         fc_update_table_usage(sour.text)) THEN
      'X'
      END) AS vupdate,
      (CASE
     WHEN depe7.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
         WHERE sour.owner = depe7.owner
        AND sour.name = depe7.name
        AND sour.type = depe7.type
        AND sour.text LIKE '%DELETE%'
        AND depe7.referenced_name =
         fc_delete_table_usage(sour.text)) THEN
      'X'
      END) AS vdelete,
      (CASE
     WHEN depe7.referenced_type IN
       ('PACKAGE',
        'PACKAGE BODY',
        'PROCEDURE',
        'FUNCTION') THEN
      'X'
      END) AS vexecute
    FROM all_dependencies depe7
   WHERE depe7.name = level6.referenced_name
     AND depe7.owner = level6.referenced_owner
     AND depe7.type = level6.referenced_type
     AND depe7.referenced_owner NOT IN
      ('SIFICO', 'PUBLIC')
   ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,,,' || level7.referenced_type || ' ' ||
       level7.referenced_owner || '.' ||
       level7.referenced_name || ',,,' ||
       level7.vselect || ',' ||
       level7.vinsert || ',' ||
       level7.vupdate || ',' ||
       level7.vdelete || ',' ||
       level7.vexecute);
FOR level8 IN (SELECT depe8.referenced_owner,
     depe8.referenced_name,
     depe8.referenced_type,
     (CASE
       WHEN depe8.referenced_type IN
         ('VIEW', 'TABLE') THEN
        'X'
     END) AS vselect,
     (CASE
       WHEN depe8.referenced_type =
         'TABLE' AND EXISTS
        (SELECT 1
         FROM dba_source sour
        WHERE sour.owner =
           depe8.owner
          AND sour.name = depe8.name
          AND sour.type = depe8.type
          AND sour.text LIKE
           '%INSERT%INTO%'
          AND depe8.referenced_name =
           fc_insert_table_usage(sour.text)) THEN
        'X'
     END) AS vinsert,
     (CASE
       WHEN depe8.referenced_type =
         'TABLE' AND EXISTS
        (SELECT 1
         FROM dba_source sour
        WHERE sour.owner =
           depe8.owner
          AND sour.name = depe8.name
          AND sour.type = depe8.type
          AND sour.text LIKE
           '%UPDATE%'
          AND depe8.referenced_name =
           fc_update_table_usage(sour.text)) THEN
        'X'
     END) AS vupdate,
     (CASE
       WHEN depe8.referenced_type =
         'TABLE' AND EXISTS
        (SELECT 1
         FROM dba_source sour
        WHERE sour.owner =
           depe8.owner
          AND sour.name = depe8.name
          AND sour.type = depe8.type
          AND sour.text LIKE
           '%DELETE%'
          AND depe8.referenced_name =
           fc_delete_table_usage(sour.text)) THEN
        'X'
     END) AS vdelete,
     (CASE
       WHEN depe8.referenced_type IN
         ('PACKAGE',
       'PACKAGE BODY',
       'PROCEDURE',
       'FUNCTION') THEN
        'X'
     END) AS vexecute
      FROM all_dependencies depe8
     WHERE depe8.name = level7.referenced_name
    AND depe8.owner =
     level7.referenced_owner
    AND depe8.type = level7.referenced_type
    AND depe8.referenced_owner NOT IN
     ('SIFICO', 'PUBLIC')
     ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,,,,' ||
      level8.referenced_owner || '.' ||
      level8.referenced_name || ',' ||
      level8.referenced_type || ',,' ||
      level8.vselect || ',' ||
      level8.vinsert || ',' ||
      level8.vupdate || ',' ||
      level8.vdelete || ',' ||
      level8.vexecute);
FOR level9 IN (SELECT depe9.referenced_owner,
       depe9.referenced_name,
       depe9.referenced_type,
       (CASE
      WHEN depe9.referenced_type IN
        ('VIEW', 'TABLE') THEN
       'X'
       END) AS vselect,
       (CASE
      WHEN depe9.referenced_type =
        'TABLE' AND EXISTS
       (SELECT 1
        FROM dba_source sour
          WHERE sour.owner =
          depe9.owner
         AND sour.name =
          depe9.name
         AND sour.type =
          depe9.type
         AND sour.text LIKE
          '%INSERT%INTO%'
         AND depe9.referenced_name =
          fc_insert_table_usage(sour.text)) THEN
       'X'
       END) AS vinsert,
       (CASE
      WHEN depe9.referenced_type =
        'TABLE' AND EXISTS
       (SELECT 1
        FROM dba_source sour
          WHERE sour.owner =
          depe9.owner
         AND sour.name =
          depe9.name
         AND sour.type =
          depe9.type
         AND sour.text LIKE
          '%UPDATE%'
         AND depe9.referenced_name =
          fc_update_table_usage(sour.text)) THEN
       'X'
       END) AS vupdate,
       (CASE
      WHEN depe9.referenced_type =
        'TABLE' AND EXISTS
       (SELECT 1
        FROM dba_source sour
          WHERE sour.owner =
          depe9.owner
         AND sour.name =
          depe9.name
         AND sour.type =
          depe9.type
         AND sour.text LIKE
          '%DELETE%'
         AND depe9.referenced_name =
          fc_delete_table_usage(sour.text)) THEN
       'X'
       END) AS vdelete,
       (CASE
      WHEN depe9.referenced_type IN
        ('PACKAGE',
         'PACKAGE BODY',
         'PROCEDURE',
         'FUNCTION') THEN
       'X'
       END) AS vexecute
     FROM all_dependencies depe9
    WHERE depe9.name =
       level8.referenced_name
      AND depe9.owner =
       level8.referenced_owner
      AND depe9.type =
       level8.referenced_type
      AND depe9.referenced_owner NOT IN
       ('SIFICO', 'PUBLIC')
    ORDER BY 1, 2) LOOP
  dbms_output.put_line(',,,,,,,,' ||
        level9.referenced_owner || '.' ||
        level9.referenced_name || ',' ||
        level9.referenced_type || ',' ||
        level9.vselect || ',' ||
        level9.vinsert || ',' ||
        level9.vupdate || ',' ||
        level9.vdelete || ',' ||
        level9.vexecute);
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END;

/

Saludos.