SQL Script

Rebuild Indexes

Updated: 11-Oct-2002
Version: 1.0

Description

Dynamically rebuild all indexes for an owner into a given tablespace

Parameters

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

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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(512);	

w_cursor_id integer;
w_p_exec integer;

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||'%') 
   and index_type not like 'IOT%' ;               /* this operation is not supported on IOT indexes */


BEGIN

   w_cursor_id:=dbms_sql.open_cursor;

   FOR ind_rec in get_ind LOOP



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

      dbms_output.put_line(sql_stmt);
  
      dbms_sql.parse(w_cursor_id, sql_stmt ,1);
      w_p_exec := dbms_sql.execute(w_cursor_id);

   END LOOP;

   dbms_sql.close_cursor(w_cursor_id);

END;
/

col segment_name form a30
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

Site Map

SQL Script

Rebuild Indexes

Updated: 11-Oct-2002Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 11-Oct-2002
Version: 1.0