Move Index and rebuild index

---first create a folder C:/Tuning

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:\TUNING\STIMG_MoveIndexes.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)

SELECT 'ALTER INDEX '||I.owner||'.'||I.index_name||CHR(10)||
'REBUILD TABLESPACE '||I.tablespace_name||' ONLINE PARALLEL;'
FROM DBA_INDEXES I,DBA_TABLES T
WHERE I.table_name = T.table_name
AND I.owner = T.owner
AND T.owner = UPPER('&app_owner');

-- 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:\TUNING\STIMG_MoveIndexes.log

@C:\TUNING\STIMG_MoveIndexes.tmp

spool off