Procedure/Function: Unterschied zwischen den Versionen

Aus MeinWiki
Wechseln zu: Navigation, Suche
(Statistic aktualisieren)
(Statistic aktualisieren)
Zeile 2: Zeile 2:
 
=== Statistic aktualisieren ===
 
=== Statistic aktualisieren ===
 
*Procedur zum Erstellen der Statistik für Tabellen.  
 
*Procedur zum Erstellen der Statistik für Tabellen.  
 +
 +
CREATE OR REPLACE PROCEDURE Call_Statistic_Table
 +
(p_schema dba_users.username%type)
 +
is
 +
-- Funktion
 +
-- 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;
 +
 +
 
     create or replace PROCEDURE Call_Statistic_Table
 
     create or replace PROCEDURE Call_Statistic_Table
 
     (p_schema dba_users.username%type)
 
     (p_schema dba_users.username%type)

Version vom 13. Juli 2016, 06:07 Uhr

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
-- Funktion
-- 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;


   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;
  /
  • Führt die beiden oberen Proceduren aus.
   create or replace PROCEDURE Call_Statistic_USER
   is
   -- Ruft statistic für user aus dem Tablespace paridb* und users auf
   -- KUE
   v_username dba_users.username%Type;
   cursor c_username is 
      select username from dba_users where default_tablespace != 'SYSTEM' and default_tablespace != 'SYSAUX' and    default_tablespace != 'UNDOTBS1' and default_tablespace != 'TEMP';
  begin
     open c_username;
     loop
         fetch c_username into v_username;
         exit when c_username%NOTFOUND;
         DBMS_OUTPUT.put_line ('PARAMETER = ' || v_username);
         Call_Statistic_Table(v_username);
         Call_Statistic_Index(v_username);
     end loop;
     close c_username;
  end;
  /
  • Scheduler installieren.
   begin
       sys.dbms_scheduler.create_job(job_name           => 'SYS."STATISTIC_USER_TABLE_INDEX"',
                                     job_type           => 'STORED_PROCEDURE',
                                     job_action         => 'Call_Statistic_USER',
                                     start_date         => to_date('28-02-2014 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                     repeat_interval    => 'Freq=Daily;Interval=1;ByHour=05;ByMinute=30',
                                     end_date           => to_date('31-12-2099 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                     job_class          => 'DEFAULT_JOB_CLASS',
                                     enabled            => true,
                                     auto_drop          => false,
                                     comments            => );
   end;
   /