SET SERVEROUTPUT ON FEEDBACK OFF

DECLARE
  CURSOR idx_rbld IS
    WITH scaler1 AS (SELECT object_name FROM user_recyclebin)
    SELECT 'ALTER INDEX '||index_name||' REBUILD ONLINE' rbld_stmt
    FROM user_indexes
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    UNION ALL
    SELECT 'ALTER INDEX '||index_name||' REBUILD PARTITION '||partition_name||' ONLINE' rbld_stmt
    FROM user_ind_partitions
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    AND partition_name NOT IN(SELECT object_name FROM scaler1)
    UNION ALL
    SELECT 'ALTER INDEX '||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE' rbld_stmt
    FROM user_ind_subpartitions
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    AND subpartition_name NOT IN(SELECT object_name FROM scaler1);
BEGIN
  FOR i IN idx_rbld
  LOOP
    BEGIN
      -- DBMS_OUTPUT.PUT_LINE(i.rbld_stmt);
      EXECUTE IMMEDIATE i.rbld_stmt;
    EXCEPTION
      WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(i.rbld_stmt);
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
  END LOOP;
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/