Moving Indexes to corresponding Index tablespaces/ different tablespace

~ Moving Indexes ~ 
~ Moving Indexes to corresponding Index tablespaces/  different tablespace

Some times slowness / perfornace issues observed in various OLTP production environments due to residing of indexes in the same tablespace. After separating idexes to different tablespace, we observed good performance. Not only it will boost performance-wise but also easy to maintain once schema objects in one tablespace and indexes in different tablespace.

syntax: ALTER INDEX index_name REBUILD TABLESPACE tablespacename ONLINE;

Caution: Don't move 'BITMAP' or 'DOMAIN' indexes.

-- Applies to:
Any version of Oracle Database ( both RAC/ Non-RAC/ ASM / Non-ASM), No downtime required. Recomended to apply only in off peak-hours.

-- Create Index tablespaces for your application modules

select 'create tablespace ' || d.username ||
       '_INDEX datafile 'u02/oradata/datafile/' || d.username || '_INDEX_01'''||' size 100M autoextend on; '
from dba_users d
where d.username in('HR','CRM','PAYROLL','SHIPPING');

Note: Add your require schema.

-- Create script move indexes to newly created tablespaces

SQL>SELECT 'ALTER INDEX ' || ai.owner || '.' || ai.index_name ||
        ' rebuild tablespace '|| ai.owner||'_INDEX online;'
FROM ALL_INDEXES ai
WHERE  ai.index_type not in ('BITMAP','DOMAIN')
AND OWNER IN ('HR','CRM','PAYROLL','SHIPPING');

-- Confirmation of Move

SQL> select owner,tablespace_name,count(1)
from all_indexes
WHERE OWNER IN ('HR','CRM','PAYROLL','SHIPPING');

Note: Keep on checking status of no. of Index moved to corresponding tablespaces.
Use SQLDEVELPOR / TOAD / PLSQL developer tool to collect sample queries to run. Other-wise follow below methods:
set echo off;
set heading off;
set lines 180;
set pages 500;    etc.
Hope this document will definitely help.

Thanks
DBAsh.........njy.........