How to generate a script to rebuild unusable indexes

set trimspool on
set lines 200
set pages 0
set heading off
set verify off
set feedback off
set echo off
spool rebuild_stmts.sql

select 'spool rebuild_stmts.log' from dual;
SELECT 'alter index ' || idx.table_owner || '.' || idx.index_name || ' rebuild ' || ' ONLINE;'
FROM dba_indexes idx
where idx.status = 'UNUSABLE'
ORDER BY idx.table_owner, idx.index_name
/
select 'exit' from dual;

exit

How to generate a script to rebuild unusable index partitions

set trimspool on
set lines     200
set pages     0
set heading   off
set verify    off
set feedback  off
set echo      off
spool rebuild_stmts.sql
--Rebuild the index partition to the same tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ';'
FROM   dba_indexes idx, dba_ind_partitions idxpart
WHERE  idx.table_owner = 'SCOTT'
AND    idx.index_name = idxpart.index_name
AND    idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--For a specific INDEX, reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = 'IDX_01'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--Use sqlplus variables, also reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = '&&index_name'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
-- Rebuild index subpartitions. Also add a parallel statement and the ONLINE keyword:
SELECT 'alter index ' || IDX.TABLE_OWNER || '.' ||  IDX.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' parallel 4 tablespace ' || IDXSUBPART.TABLESPACE_NAME ||' online;'
FROM    DBA_INDEXES IDX, DBA_IND_SUBPARTITIONS IDXSUBPART
WHERE   IDX.TABLE_OWNER IN ('SCOTT','JAMES')
AND     IDX.INDEX_NAME = IDXSUBPART.INDEX_NAME
AND     IDX.INDEX_NAME IN (SELECT UNIQUE INDEX_NAME
                                        FROM DBA_IND_SUBPARTITIONS
                                        WHERE STATUS = 'UNUSABLE')
AND INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND IDXSUBPART.STATUS <> 'USABLE'
ORDER BY IDX.OWNER, IDX.INDEX_NAME
/

Upon completion, the file "rebuild_stmts.sql" should contain your "ALTER INDEX REBUILD PARTITION..." directives.