ALTER INDEX REBUILD ONLINE in Partitioned Index

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

SET LINESIZE 140

COL sql_cmd FOR A120

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

spool rebuild_ix.sql

SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||PARTITION_NAME||' ONLINE;' AS sql_cmd

  FROM DBA_IND_PARTITIONS

 WHERE index_name = 'MY_INDEX';

spool off

--Same for a user

SELECT 'ALTER INDEX '||index_name||' REBUILD PARTITION '||PARTITION_NAME||' ONLINE;' AS sql_cmd
  FROM USER_IND_PARTITIONS
 WHERE index_name = 'MY_INDEX';

SQL_CMD
------------------------------------------------------------------
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P392 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P393 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P394 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P395 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P396 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P397 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P398 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P399 ONLINE;

Before rebuild:

SEGMENT_NAME                   USED_MB

------------------------------ --------------

MY_INDEX                                 3567

After rebuild

SEGMENT_NAME                   USED_MB

------------------------------ --------------

MY_INDEX                                  2301