Procedure/Function: Unterschied zwischen den Versionen

Aus MeinWiki
Wechseln zu: Navigation, Suche
(Die Seite wurde neu angelegt: „== Procedure == === Statistic aktualisieren === *Procedur zum Erstellen der Statistik für Tabellen. create or replace PROCEDURE Call_Statistic_Table…“)
 
(Statistic aktualisieren)
Zeile 11: Zeile 11:
 
       select table_name from dba_tables where owner = p_schema;  
 
       select table_name from dba_tables where owner = p_schema;  
 
     begin
 
     begin
     DBMS_OUTPUT.put_line ('PARAMETER = ' || p_schema);
+
     DBMS_OUTPUT.put_line ('PARAMETER = ' || p_schema)
open c_tablename;
+
        open c_tablename;
loop
+
        loop
  fetch c_tablename into v_tablename;
+
          fetch c_tablename into v_tablename;
  exit when c_tablename%NOTFOUND;
+
          exit when c_tablename%NOTFOUND;
 
  begin
 
  begin
 
  dbms_stats.gather_table_stats(p_schema, v_tablename);
 
  dbms_stats.gather_table_stats(p_schema, v_tablename);

Version vom 23. Januar 2015, 08:28 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
   -- 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;
   /