How to find the list of unusable index on the full database:-

set pagesize 9000
set linesize 2000
set long 20000

select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in(SELECT OWNER from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')))
AND INDEX_NAME IN(SELECT INDEX_NAME from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')));

===============

Schema Wise

set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE') where OWNER='&schema_name';
spool off


===============
Generating the unusable command:

[oracle@xxxx swarn]$ more 2.sql
spool /u01/oracle/swarn/unusable_cmd.sql
set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
set heading off
select 'alter index '||OWNER||'.'||index_name||' unusable; 'from (select OWNER, INDEX_NAME from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE'));
spool off
====================