--   Rebuild all non-partitioned normal or bitmap
--                indexes for NBPTS and FIN schemas--
--                Online option of Index Rebuild NOT used  
--        Note:  Must be run when users are not likely to be using the database

DECLARE 

sql_stmt varchar2(254); 

cursor 
get_ind is 
select owner,index_name,tablespace_name
from dba_indexes 
where owner IN ('NBPTS','FIN') 
and index_type in ('NORMAL','BITMAP')
and partitioned = 'NO'
order by owner,index_name; 
BEGIN 
FOR ind_rec in get_ind 
  LOOP 
    sql_stmt := 'alter index '||ind_rec.owner||'.'||ind_rec.index_name ||' rebuild tablespace '||ind_rec.tablespace_name || ' compute statistics'; 
     dbms_output.put_line(sql_stmt); 
     EXECUTE IMMEDIATE sql_stmt;  
  END LOOP; 

END; 
/ 

select 'cdmsp_dynamic_ndx_rebuild.sql :' "Program",to_char(sysdate,'YYYY-MON-DD HH24:MI:SS')  "End Time"
from dual;