How to automatically rebuild partitioned local index

Once in a while somebody gets a new job in a new company  and what's more every now and again it happens in your office !

This has been Sebastano's turn, a co-worker of mine. 

He went to England for a study leave and when he got there after a while he got a new job too.

Due to his leaving now I have to do also his previous work and rebuilding partitioned local index is one of those.

At first glance it seems to be a boring and time consuming task, but why on earth can't I automate this procedure ?

Following you'll find a complete PL/SQL Block to do it.

The PL/SQL block is going to ask only the degree of parallelism and what's more the schema name.

A "rebuild_local_index.sql" file will be produced by the sqlplus client later on.

You can use it to rebuild all your partitioned local index that are owned by a schema name.

PS: Who is writing doesn't know your environment so try this procedure out before using on a production database.

See you in my next blog !

Byeeeee

***************************************************

SET serveroutput on
set lin 300
accept vParamParallelism prompt "Degree: ";
accept vParamUtente prompt "SchemaName: ";

set echo off
set heading off
set feedback off
set term off
set lin 200
set verify off

spool rebuild_local_index.sql
select 'set echo on' from dual;
select 'set feedback on' from dual;

declare
vParallelismo varchar2(2);
vUtente varchar2(50);
vSQL varchar2(255);
vSQL_LOG_OFF varchar2(255);

begin
vParallelismo:='&vParamParallelism';
vUtente:='&vParamUtente';

for riga_cursore in (select 'alter index ' || INDEX_OWNER ||'.'|| INDEX_NAME || ' rebuild partition ' || PARTITION_NAME || ' nologging parallel '||vParallelismo||' ;' testo_sql FROM dba_ind_partitions i where index_owner=vUtente and status != 'USABLE' and partition_name !='PMAX' order by index_name)
loop
vSQL:=riga_cursore.testo_sql;
dbms_output.put_line(vSQL);
end loop;

for riga_cursore_log_off in (select 'alter index '|| vUtente ||'.'|| index_name||' logging noparallel;' testo_sql_log_off FROM dba_ind_partitions where index_owner=vUtente and LOGGING='NO' and partition_name !='PMAX' group by index_name)
loop
vSQL_LOG_OFF:=riga_cursore_log_off.testo_sql_log_off;
dbms_output.put_line(vSQL_LOG_OFF);
end loop;


end;
/

spool off
set term on