Procedure/Function

Procedure

Statistic aktualisieren

• Procedur zum Erstellen der Statistik für Tabellen.

   create or replace PROCEDURE Call_Statistic_Table
   (p_schema dba_users.username%type)
   is
   -- Erstellt eine neue Statisitic von Tabellen
   -- KUE
   v_tablename dba_tables.table_name%Type;
   cursor c_tablename is 
      select table_name from dba_tables where owner = p_schema; 
   begin
   DBMS_OUTPUT.put_line ('PARAMETER = ' || p_schema)
       open c_tablename;
       loop
         fetch c_tablename into v_tablename;
         exit when c_tablename%NOTFOUND;
         begin
            dbms_stats.gather_table_stats(p_schema, v_tablename);
            exception
            when others then
            dbms_output.put_line('Error!' || p_schema );
         end;
       end loop;
       close c_tablename;
   end;
   / 

• Procedur zum Erstellen der Statistik für Indexe.

  create or replace PROCEDURE Call_Statistic_Index
  (p_schema dba_users.username%type)
  is
  -- Erstellt eine neue Statisitic von Index
  -- KUE
  v_indexname dba_indexes.index_name%Type;
  cursor c_indexname is 
      select index_name from dba_indexes where owner = p_schema; 
  begin
     DBMS_OUTPUT.put_line ('PARAMETER = ' || p_schema);
     open c_indexname;
     loop
        fetch c_indexname into v_indexname;
        exit when c_indexname%NOTFOUND;
        begin
           dbms_stats.gather_index_stats(p_schema, v_indexname);
           exception
           when others then
           dbms_output.put_line('Error!' || p_schema );
        end;
     end loop;
     close c_indexname;
  end;
  /