In general is good to have a separate tablespace for your index tables.
Sometimes the default tablespace, in our case DATA, is used by an application using hibernate layer to automatically create all the tables and indexes.
Then the issue is how to move the automatically created indexes to the special created tablespace for indexes, in our case INDEX.
The following SQL query will generate all the ALTER statements we need to move all indexes.
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'INDEX online parallel 8;' FROM dba_indexes where tablespace_name='DATA' AND owner= 'my_user' AND index_name NOT LIKE 'SYS_IL%';
Note the following:
- make sure to filter by owner, my_user in my case as you can update only your indexes.
- make sure to exclude the LOB indexes, if you have them. In ORACLE their name is preceded by SYS_IL
The generated alter statements will look like:
ALTER INDEX my_user.SYS_C0011690 REBUILD TABLESPACE INDEX;If you want to move also the LOB segment and LOB indexes (they should be always moved together) the following script will generate the necessary queries
select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE LOBTABLESPACE) parallel 96;' from dba_lobs where tablespace_name='DATA';
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' TABLESPACE LOBTABLESPACE;' from dba_ind_partitions where tablespace_name='DATA';
select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE partition '||Partition_name||' lob('||column_name||')'||' STORE AS (TABLESPACE LOBTABLESPACE) ;' from dba_lob_partitions where TABLESPACE_NAME = 'DATA';see the following post for details How to Move Lob Segment and Lob Indexes to Another Tablespace in Oracle