Procedure/Function: Unterschied zwischen den Versionen
Aus MeinWiki
(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; | |
− | + | loop | |
− | + | fetch c_tablename into v_tablename; | |
− | + | 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; /