How to rebuild an index subpartition

To limit a rebuild to a specific index subpartition only, use the following syntax:

ALTER INDEX SCOTT.SALES_IDX3
  REBUILD SUBPARTITION SALES_IDX3_SP_2017_06 ONLINE
  TABLESPACE DATA2;

Notice the use of the ONLINE keyward, which will allow DML against the table (and thus update the index).

To generate rebuild-statements, one index at a time, I have used the script below.

It takes as arguments
1. The index owner
2. The index name
3. The new tablespace where you want to place your subpartitions
4. The old tablespace from which you want to move out

set termout off
select 'alter session set nls_language=''american'';' from dual;
set termout on
accept index_owner prompt 'Index owner: '
accept index_name prompt 'Index name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_subpart_&&index_owner..&&index_name..sql
select 'alter session set nls_language=''american'';' from dual;

select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_rebuild_subpart_&&index_owner..&&index_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''Rebuild_supbart_&&index_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''Rebuild_supbart_t_&&index_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''Rebuild_supbart_&&index_name''); ' from dual;

select 'prompt moving the index subpartitions from &&old_tabspc to &&new_tabspc,' from dual;
select 'prompt and setting default attributes for the involved partitions. ' from dual;
SELECT 'ALTER INDEX ' ||  IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' tablespace &&new_tabspc ONLINE;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
union
select distinct 'alter index ' ||   IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' modify default attributes for partition ' || idxsubpart.partition_name || ' tablespace &&new_tabspc;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION';

select 'exit' from dual;
exit

Run it as a user with access to the data dictionary:

sqlplus / as sysdba @gen_move_idx_subpart.sql

It will generate a new script called exec_move_subpart_<username>.<index_name>.sql which will actually perform the rebuild:

sqlplus / as sysdba @exec_move_subpart_<username>.<index_name>.sql

I also added som calls to dbms_application_info so that the session can be easily found in v$session