Rebuilden van indexen en verplaatsen van tabellen in ORACLE

Voor de performance van je Oracle database is het nodig regelmatig je tabellen en indexen te analyseren. Ik ga niet verder in op hoe de principes precies werken, wel heb ik hier een aantal handige scripts die bulksgewijs je indexen analyseren, rebuilden en verplaatsen naar een andere tablespace. Tevens een script om alle tabellen te verplaatsen naar een andere tablespace.

Onderstaande scripts kun je uitvoeren in SQLPlus. Vervang ==OWNER== door de eigenaar van de tabellen waar het over gaat. Je moet ook inloggen als deze user om het script uit te voeren, anders werkt het niet.

Rebuild all indexes
Met onderstaande script kunnen alle indexen van ==OWNER== gerebuild worden (inloggen als ==OWNER==!):
set serveroutput on size 1000000
declare
v_errmsg VARCHAR2(256);
cursor c_indexes is
select index_name
from dba_indexes
where owner = '==OWNER==';
begin
for r_index in c_indexes loop
begin
execute immediate('ALTER INDEX ' || r_index.index_name || ' REBUILD ONLINE');
exception
when others then
v_errmsg := SQLERRM;
dbms_output.put_line(r_index.index_name || ': ' || v_errmsg);
end;
end loop;
end;
/


Analyze all indexes
Met onderstaande script kunnen alle indexen worden geanalysed:

set serveroutput on size 1000000
declare
v_errmsg VARCHAR2(256);
v_h integer;
v_r integer;
v_dr integer;
cursor c_indexes is
select index_name
from dba_indexes
where owner = '==OWNER==';
begin
for r_index in c_indexes loop
begin
--execute immediate('ALTER INDEX ' || r_index.index_name || ' REBUILD ONLINE');
execute immediate 'analyze index ' || r_index.index_name || ' compute statistics';
execute immediate 'analyze index ' || r_index.index_name || ' validate structure';
exception
when others then
v_errmsg := SQLERRM;
dbms_output.put_line(r_index.index_name || ': ' || v_errmsg);
end;
end loop;
end;
/


Verplaatsen alle indexen naar een andere tablespace

Vanwege esthetische aspecten en performance overwegingen is het handig je indexen in een andere tablespace op te slaan dan je tabellen. Na het maken van een aparte tablespace kun je de indexen van een gebruiker (==OWNER==) verplaatsen naar betreffende tablespace (==TARGETTABLESPACE==).

Verplaatsen met onderstaande query:
set serveroutput on size 1000000
declare
v_errmsg VARCHAR2(256);
cursor c_indexes is
select index_name
from dba_indexes
where owner = '==OWNER=='
and tablespace_name != '==TARGETTABLESPACE==';
begin
for r_index in c_indexes loop
begin
execute immediate('ALTER INDEX ' || r_index.index_name || ' REBUILD TABLESPACE "==TARGETTABLESPACE=="');
exception
when others then
v_errmsg := SQLERRM;
dbms_output.put_line(r_index.index_name || ': ' || v_errmsg);
end;
end loop;
end;
/


Verplaatsen van alle tabellen die niet in de ==OWNER== tablespace staan en rebuilden van verwante indexen

set serveroutput on size 1000000
declare
v_errmsg VARCHAR2(256);
cursor c_tables is
select table_name
from dba_tables
where owner = '==OWNER=='
and tablespace_name != '==OWNER==';
cursor c_indexes (p_tablename in varchar2) is
select index_name
from dba_indexes
where table_name = p_tablename;
begin
for r_table in c_tables loop
begin
--tabel verplaatsen
execute immediate('ALTER TABLE ' || r_table.table_name || ' MOVE TABLESPACE ==OWNER==');
--indexen rebuilden van betreffende table
for r_index in c_indexes(r_table.table_name) loop
execute immediate('ALTER INDEX ' || r_index.index_name || ' REBUILD ONLINE');
end loop;
exception
when others then
v_errmsg := SQLERRM;
dbms_output.put_line(r_table.table_name || ': ' || v_errmsg);
end;
end loop;
end;
/