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.
martes, 14 de agosto de 2012
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.
Suscribirse a:
Entradas (Atom)