Procedure/Function
Aus MeinWiki
Version vom 23. Januar 2015, 08:28 Uhr von Admwiki (Diskussion | Beiträge) (→Statistic aktualisieren)
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; /