SQL Script Rebuild Indexes

Updated : 18-Apr-2002
Version : 1.0

Description

Dymanically rebuild all indexes for an owner into a given tablespace

Parameters

&1 - Index Owner
&2 - Index Name
&3 - Tablespace Name

SQL Source

set verify off
set pause off
set doc off
set heading off

spool alterindex.lst

rem define iowner = UPPER('&1')
rem define iname = UPPER('&2')
rem define tspace = UPPER('&3')

accept iowner prompt'Enter Index Owner : '
accept iname prompt'Enter Index Name (RETURN for all) : '
accept tspace prompt'Enter Index Tablespace Name  to Rebuild to : ' 

prompt
prompt 'Rebuilding Indexes for &iowner to index tablespace &tspace '
prompt

set serverout on


DECLARE	

sql_stmt varchar2(254);	

l_target_tspace user_tablespaces.tablespace_name%TYPE := '&tspace';
l_index_owner all_indexes.owner%TYPE := '&iowner';
l_ind_name all_indexes.indeX_name%TYPE  := '&iname';

cursor get_ind is
   select owner,index_name from dba_indexes
   where owner = UPPER(l_index_owner)
   and indeX_name like UPPER(l_ind_name||'%') ;

BEGIN

   FOR ind_rec in get_ind LOOP

      sql_stmt := 'alter index '||ind_rec.owner||'.'||ind_rec.index_name
               ||' rebuild online tablespace '||l_target_tspace;

      dbms_output.put_line(sql_stmt);
  
      EXECUTE IMMEDIATE sql_stmt;

   END LOOP;

END;
/

col segment_name form a20
col segment_type form a6
col tablespace_name form a12

select segment_name,segment_type,tablespace_name
from dba_segments
where segment_type in ('TABLE','INDEX') 
and owner  = UPPER('&iowner')
and segment_name like UPPER('&iname'||'%')
/

spool off

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal