How to move Indexes into another TableSpace

Question

how to move indexes to another tablespace

select owner, index_name, TABLE_NAME, a.TABLESPACE_NAME 
from all_indexes a where a.TABLE_NAME = 'REFUND';

ALTER INDEX CBS.PK_REFUND_ID REBUILD TABLESPACE T_IDX;

ORA-14086: A partitioned index may not be rebuilt as a whole.

I can't execute this statement because I don't know in which partition the index is

ALTER INDEX PK_REFUND_ID REBUILD PARTITION xxxxxx TABLESPACE T_IDX;

Answer 1

Because you also need partition name info which can be reached from user[all/dba]_tab_partitions dictionary view. If you got the related partition name, then

ALTER INDEX PK_REFUND_ID REBUILD PARTITION PR_REFUND_OLD [ONLINE];

might be used just to rebuild the index.

or

ALTER INDEX PK_REFUND_ID REBUILD PARTITION PR_REFUND_OLD [ONLINE] TABLESPACE T_IDX;

might be used to move the index of the partition to a different tablespace. Using ONLINE option would suit well for the tables currently having DML activity.

Use the following code in order to run as a batch job

DECLARE
  v_ts_name VARCHAR2(35) := 'T_IDX';
BEGIN
  FOR c IN
    (  
    SELECT 'ALTER INDEX '||i.index_name||' REBUILD PARTITION '||p.partition_name||
          ' ONLINE TABLESPACE '||v_ts_name AS command
      FROM user_part_indexes i
      JOIN user_ind_partitions p
        ON p.index_name = i.index_name 
       AND i.table_name = 'REFUND'
       AND p.tablespace_name != v_ts_name
    )
  LOOP
    EXECUTE IMMEDIATE c.command;
  END LOOP;   
END;
/