0

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';

enter image description here

ALTER INDEX CBS.PK_REFUND_ID REBUILD TABLESPACE T_IDX;

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

enter image description here

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;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Tomas
  • 47
  • 1
  • 5
  • Maybe run `alter session set nls_language = 'American'` in order to get an english error message. And please don't paste images, see https://meta.stackoverflow.com/a/285557/3027266 – Wernfried Domscheit Jan 31 '21 at 13:06

1 Answers1

3

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;
/
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • 1
    I think you should better query view `USER_IND_PARTITIONS`, because `p.partition_name` is the partition name of the **table** partition. The partition name of the index can be different than the partition name of the table - they can be even fully independent, see https://stackoverflow.com/questions/57107906/global-nonpartitioned-index-in-oracle/57112707#57112707 – Wernfried Domscheit Jan 31 '21 at 13:11