Rebuild indexes

set echo off
set feedback off
set pagesize 0
set verify off

PROMPT Enter the name of the application owner:

ACCEPT app_owner

PROMPT Enter the name of the new tablespace for the application indexes:

ACCEPT new_idx_tablespace

spool c:\ONE\MoveIndexes_STLBAS.tmp

-- rebuild all indexes on the moved tables, even those not owned
-- by the specified user because moving the tables will set their
-- status to UNUSABLE (unless they are IOT tables)

-- rebuild any other indexes owned by this user that may not be on
-- the above tables

SELECT 'ALTER INDEX '||owner||'.'||index_name||CHR(10)||
'REBUILD TABLESPACE &new_idx_tablespace ONLINE PARALLEL;'
FROM dba_indexes
WHERE owner = UPPER('&&app_owner');

spool off

set echo on
set feedback on
set pagesize 60

spool c:\ONE\MoveIndexes_STLBAS.log

@c:\ONE\MoveIndexes_STLBAS.tmp

spool off