Download AC-Toolbar Oracle Schulung und Consulting

PL/SQL-Objektabhängigkeiten

Benutzerbewertung: / 441
SchwachPerfekt 
Geschrieben von: Marek Adar   

Oft wird die Frage gestellt, wie können Abhängigkeiten von PL/SQL-Objekten analysiert werden. Hierfür liefert die View DBA_DEPENDENCIES zwar die direkten, leider nicht die indirekten Abhängigkeiten. Die folgenden Prozeduren und Funktionen geben hier Hilfe.

Die folgende Prozedur gibt die von einem übergebenen Objekt abhängigen Objekte zurück:

create or replace procedure prc_get_dependencies(v_object_type varchar2,
                                                  v_object_name varchar2,
                                                  v_owner varchar2,
                                                  v_level number default 1)
is
  cursor c_get_dept
  is
  select type,name,owner
  from dba_dependencies
  where referenced_owner=v_owner and
        referenced_name=v_object_name and
        referenced_type=v_object_type;
begin
  dbms_output.put_line('|' || lpad(' ',v_level*5-5,' ') || v_object_type || '=>' || v_owner || '.' || v_object_name); 
  for rec_get_dept in c_get_dept
  loop
    prc_get_dependencies(rec_get_dept.type,rec_get_dept.name,rec_get_dept.owner,v_level+1);
  end loop;
end;
/

SQL> set serveroutput on
SQL> exec prc_get_dependencies('TABLE','EMPLOYEES','HR')
|TABLE=>HR.EMPLOYEES
|     VIEW=>HR.EMP_DETAILS_VIEW
|     TRIGGER=>HR.UPDATE_JOB_HISTORY
|     VIEW=>HR.V_AVSAL

PL/SQL procedure successfully completed.

Entsprechende Abänderung der Prozedur, für die Durchführung der abhängigen Kompilierung:

create or replace procedure prc_compile_dependencies(v_object_type varchar2,
                                                  v_object_name varchar2,
                                                  v_owner varchar2,
                                                  v_level number default 1)
is
  cursor c_get_dept
  is
  select type,name,owner
  from dba_dependencies
  where referenced_owner=v_owner and
        referenced_name=v_object_name and
        referenced_type=v_object_type;
  v_status_count number:=0;
  v_compiled varchar2(100);
begin
   
  select count(*)
  into v_status_count
  from dba_objects
  where owner=v_owner and object_name=v_object_name and 
        object_type=v_object_type and status='INVALID';
  
  if v_status_count>0 then
    begin
v_compiled:='COMPILATION SUCCESSFUL!';
case v_object_type
  when 'PROCEDURE' then
execute immediate 'alter procedure ' || v_owner || '.' || 
v_object_name || ' compile';
  when 'FUNCTION' then
execute immediate 'alter function ' || v_owner || '.' || 
v_object_name || ' compile';
  when 'PACKAGE' then
execute immediate 'alter package ' || v_owner || '.' || 
v_object_name || ' compile';
  when 'PACKAGE BODY' then
execute immediate 'alter package ' || v_owner || '.' || 
v_object_name || ' compile body';
  when 'TYPE' then
execute immediate 'alter type ' || v_owner || '.' || 
v_object_name || ' compile';
  when 'TYPE BODY' then
execute immediate 'alter type ' || v_owner || '.' || 
v_object_name || ' compile body';
  when 'VIEW' then
execute immediate 'alter view ' || v_owner || '.' || 
v_object_name || ' compile';
  when 'TRIGGER' then
execute immediate 'alter trigger ' || v_owner || '.' || 
v_object_name || ' compile';
  else
v_compiled:='COMPILATION FOR THIS OBJECT NOT SUPPORTED!';
    end case;
    exception
      when others
        then
          v_compiled:='COMPILATION FAILED!';
    end;
  else
    v_compiled:='NO COMPILATION NEEDED!';
  end if;
  dbms_output.put_line('|' || lpad(' ',v_level*5-5,' ') || v_object_type || '=>' || 
                       v_owner || '.' || v_object_name || '=>' || v_compiled);
  for rec_get_dept in c_get_dept
  loop
    prc_compile_dependencies(rec_get_dept.type,rec_get_dept.name,rec_get_dept.owner,v_level+1);
  end loop;
end;
/

SQL> exec prc_compile_dependencies('TABLE','JOB_HISTORY','HR')
|TABLE=>HR.JOB_HISTORY=>NO COMPILATION NEEDED!
|     PROCEDURE=>HR.ADD_JOB_HISTORY=>NO COMPILATION NEEDED!
|          TRIGGER=>HR.UPDATE_JOB_HISTORY=>NO COMPILATION NEEDED!

PL/SQL procedure successfully completed.

Erweiterung der Prozedur für die Überprüfung aller Objekte auf Ungültigkeit und Kompilierung der abhängigen Objekte, falls notwendig:

create or replace procedure prc_compile_schema(v_schema varchar2)
is
  cursor c_independent_objects(p_schema varchar2)
  is
  (select referenced_owner, referenced_type,referenced_name
  from dba_dependencies where referenced_owner=p_schema
  minus
  select owner,type,name from dba_dependencies)
  union
  (select owner, type,name from dba_dependencies
  where owner=p_schema
  minus
  select owner, type,name from dba_dependencies
  where owner=p_schema and referenced_owner=p_schema);
  
  
  procedure prc_compile_dependencies(v_object_type varchar2,
                                     v_object_name varchar2,
                                     v_owner varchar2,
                                     v_level number default 1)
  is
    cursor c_get_dept
    is
    select type,name,owner
    from dba_dependencies
    where referenced_owner=v_owner and
          referenced_name=v_object_name and
          referenced_type=v_object_type;
    v_status_count number:=0;
    v_compiled varchar2(100);
  begin
     
    select count(*)
    into v_status_count
    from dba_objects
    where owner=v_owner and object_name=v_object_name and 
object_type=v_object_type and status='INVALID';
    
    if v_status_count>0 then
      begin
  v_compiled:='COMPILATION SUCCESSFUL!';
      case v_object_type
        when 'PROCEDURE' then
          execute immediate 'alter procedure ' || v_owner || '.' || 
  v_object_name || ' compile';
        when 'FUNCTION' then
          execute immediate 'alter function ' || v_owner || '.' || 
  v_object_name || ' compile';
        when 'PACKAGE' then
          execute immediate 'alter package ' || v_owner || '.' || 
  v_object_name || ' compile';
        when 'PACKAGE BODY' then
          execute immediate 'alter package ' || v_owner || '.' || 
  v_object_name || ' compile body';
        when 'TYPE' then
          execute immediate 'alter type ' || v_owner || '.' || 
  v_object_name || ' compile';
        when 'TYPE BODY' then
          execute immediate 'alter type ' || v_owner || '.' || 
  v_object_name || ' compile body';
        when 'VIEW' then
          execute immediate 'alter view ' || v_owner || '.' || 
  v_object_name || ' compile';
        when 'TRIGGER' then
          execute immediate 'alter trigger ' || v_owner || '.' || 
  v_object_name || ' compile';
else
v_compiled:='COMPILATION FOR THIS OBJECT NOT SUPPORTED!';
      end case;
       v_compiled:='COMPILATION SUCCESSFUL!';
      exception
        when others
          then
            v_compiled:='COMPILATION FAILED!';
      end;
    else
      v_compiled:='NO COMPILATION NEEDED!';
    end if;
    dbms_output.put_line('|' || lpad(' ',v_level*5-5,' ') || v_object_type || '=>' || 
 v_owner || '.' || v_object_name || '=>' || v_compiled);
    for rec_get_dept in c_get_dept
    loop
      prc_compile_dependencies(rec_get_dept.type,
                           rec_get_dept.name,
   rec_get_dept.owner,
   v_level+1);
    end loop;
  end prc_compile_dependencies;
    
begin
  if v_schema='SYS'
  then
     raise_application_error(-20000,'Don''t use SYS for compilation!');
  end if;
  for rec_independent_objects in c_independent_objects(v_schema)
  loop
    prc_compile_dependencies(rec_independent_objects.referenced_type,
                         rec_independent_objects.referenced_name,
 rec_independent_objects.referenced_owner);
  end loop;
end;
/

SQL> exec prc_compile_schema('HR')
|PROCEDURE=>HR.PRC_COMPILE_DEPENDENCIES=>NO COMPILATION NEEDED!
|PROCEDURE=>HR.PRC_COMPILE_SCHEMA=>NO COMPILATION NEEDED!
|PROCEDURE=>HR.PRC_GET_DEPENDENCIES=>NO COMPILATION NEEDED!
|PROCEDURE=>HR.PRC_SHOW_DEPENDENCIES=>NO COMPILATION NEEDED!
|PROCEDURE=>HR.SECURE_DML=>NO COMPILATION NEEDED!
|TABLE=>HR.COUNTRIES=>NO COMPILATION NEEDED!
|     VIEW=>HR.EMP_DETAILS_VIEW=>NO COMPILATION NEEDED!
|TABLE=>HR.DEPARTMENTS=>NO COMPILATION NEEDED!
|     VIEW=>HR.EMP_DETAILS_VIEW=>NO COMPILATION NEEDED!
|TABLE=>HR.EMPLOYEES=>NO COMPILATION NEEDED!
|     VIEW=>HR.EMP_DETAILS_VIEW=>NO COMPILATION NEEDED!
|     TRIGGER=>HR.UPDATE_JOB_HISTORY=>NO COMPILATION NEEDED!
|     VIEW=>HR.V_AVSAL=>NO COMPILATION NEEDED!
|TABLE=>HR.JOBS=>NO COMPILATION NEEDED!
|     VIEW=>HR.EMP_DETAILS_VIEW=>NO COMPILATION NEEDED!
|TABLE=>HR.JOB_HISTORY=>NO COMPILATION NEEDED!
|     PROCEDURE=>HR.ADD_JOB_HISTORY=>NO COMPILATION NEEDED!
|          TRIGGER=>HR.UPDATE_JOB_HISTORY=>NO COMPILATION NEEDED!
|TABLE=>HR.LOCATIONS=>NO COMPILATION NEEDED!
|     VIEW=>HR.EMP_DETAILS_VIEW=>NO COMPILATION NEEDED!
|TABLE=>HR.REGIONS=>NO COMPILATION NEEDED!
|     VIEW=>HR.EMP_DETAILS_VIEW=>NO COMPILATION NEEDED!
|TYPE=>HR.TYPE_GRADES=>NO COMPILATION NEEDED!
|TYPE=>HR.TYPE_HIERARCHY=>NO COMPILATION NEEDED!
|     TYPE=>HR.TYPE_TAB_HIERARCHY=>NO COMPILATION NEEDED!
|TYPE=>HR.TYPE_TAB_DEPENDENT_OBJECT=>NO COMPILATION NEEDED!
|     FUNCTION=>HR.FNC_SHOW_DEPENDENCIES=>NO COMPILATION NEEDED!
|TYPE=>HR.TYP_CHAR=>NO COMPILATION NEEDED!
|     TYPE=>HR.TYP_TAB_CHAR=>NO COMPILATION NEEDED!
|TYPE=>HR.TYP_PERSON=>NO COMPILATION NEEDED!

PL/SQL procedure successfully completed.

Zur Anzeige der Abhängigkeiten der Objekte eines Schemas über eine Funktion kann die nachfolgende  Tabellenfunktion verwendet werden:

create or replace type type_tab_dependent_object
as table of varchar2(1000)
/

create or replace function fnc_show_dependencies(v_schema varchar2)
return type_tab_dependent_object
is
  cursor c_independent_objects(p_schema varchar2)
is
(select referenced_owner, referenced_type,referenced_name
from dba_dependencies where referenced_owner=p_schema
minus
select owner,type,name from dba_dependencies)
union
(select owner, type,name from dba_dependencies
where owner=p_schema
minus
select owner, type,name from dba_dependencies
where owner=p_schema and referenced_owner=p_schema);

tab_dependent_object type_tab_dependent_object:=type_tab_dependent_object();

procedure prc_get_dependencies(v_object_type varchar2,
   v_object_name varchar2,
   v_owner varchar2,
   v_level number default 1)
is
  cursor c_get_dept
  is
  select type,name,owner
  from dba_dependencies
  where referenced_owner=v_owner and
referenced_name=v_object_name and
referenced_type=v_object_type;

v_row varchar2(1000);

begin

  v_row:='|' || lpad(' ',v_level*5-5,' ') || v_object_type || '=>' || v_owner || '.' || v_object_name;
  tab_dependent_object.extend;
  tab_dependent_object(tab_dependent_object.count):=v_row;
  for rec_get_dept in c_get_dept
  loop
prc_get_dependencies(rec_get_dept.type,rec_get_dept.name,rec_get_dept.owner,v_level+1);
  end loop;
end prc_get_dependencies;
begin

  if v_schema='SYS'
  then
raise_application_error(-20000,'Don''t use SYS for selection!');
  end if;
 
  for rec_independent_objects in c_independent_objects(v_schema)
  loop
    prc_get_dependencies(rec_independent_objects.referenced_type,
                     rec_independent_objects.referenced_name,
 rec_independent_objects.referenced_owner);
  end loop;
  return tab_dependent_object;
end;
/

SQL> col column_value format a100 

SQL> set pagesize 10000
SQL> select * from table(fnc_show_dependencies('HR'));

COLUMN_VALUE
--------------------------------------------------------
|PROCEDURE=>HR.PRC_COMPILE_DEPENDENCIES
|PROCEDURE=>HR.PRC_COMPILE_SCHEMA
|PROCEDURE=>HR.PRC_GET_DEPENDENCIES
|PROCEDURE=>HR.PRC_SHOW_DEPENDENCIES
|PROCEDURE=>HR.SECURE_DML
|TABLE=>HR.COUNTRIES
|     VIEW=>HR.EMP_DETAILS_VIEW
|TABLE=>HR.DEPARTMENTS
|     VIEW=>HR.EMP_DETAILS_VIEW
|TABLE=>HR.EMPLOYEES
|     VIEW=>HR.EMP_DETAILS_VIEW
|     TRIGGER=>HR.UPDATE_JOB_HISTORY
|     VIEW=>HR.V_AVSAL
|TABLE=>HR.JOBS
|     VIEW=>HR.EMP_DETAILS_VIEW
|TABLE=>HR.JOB_HISTORY
|     PROCEDURE=>HR.ADD_JOB_HISTORY
|          TRIGGER=>HR.UPDATE_JOB_HISTORY
|TABLE=>HR.LOCATIONS
|     VIEW=>HR.EMP_DETAILS_VIEW
|TABLE=>HR.REGIONS
|     VIEW=>HR.EMP_DETAILS_VIEW
|TYPE=>HR.TYPE_GRADES
|TYPE=>HR.TYPE_HIERARCHY
|     TYPE=>HR.TYPE_TAB_HIERARCHY
|TYPE=>HR.TYPE_TAB_DEPENDENT_OBJECT
|     FUNCTION=>HR.FNC_SHOW_DEPENDENCIES
|TYPE=>HR.TYP_CHAR
|     TYPE=>HR.TYP_TAB_CHAR
|TYPE=>HR.TYP_PERSON

30 rows selected.

 

©Adar-Consult, Ihr Partner für Oracle Schulungen, Seminare, Workshops und Consulting

Oracle Training und Schulungen
Tag Cloud
sql-grundlagen oracle-datenbankadministration oracle-check, export poster sql-tuning kleine oracle recovery google import rac-praxisworkshop excel schulungsangebot google404, plan, sql-pl/sql-bootcamp oracle-architekturposter hochverfügbarkeits-bootcamp packages application pl/sql-entwicklung pl/sql sql-commander cluster
AC-Blog