-- Prompt for schema ACCEPT schema_name CHAR PROMPT 'Enter schema name: ' -- Spool to log file SPOOL rebuild_indexes_&&schema_name..log SET SERVEROUTPUT ON SIZE UNLIMITED DECLARE v_sql VARCHAR2(4000); v_owner VARCHAR2(128) := UPPER('&&schema_name'); v_unusable_before NUMBER := 0; v_unusable_after NUMBER := 0; v_parallel_before NUMBER := 0; v_parallel_after NUMBER := 0; BEGIN -- Count unusable indexes before SELECT COUNT(*) INTO v_unusable_before FROM ( SELECT 1 FROM dba_indexes WHERE owner = v_owner AND status = 'UNUSABLE' UNION ALL SELECT 1 FROM dba_ind_partitions WHERE index_owner = v_owner AND status = 'UNUSABLE' UNION ALL SELECT 1 FROM dba_ind_subpartitions WHERE index_owner = v_owner AND status = 'UNUSABLE' ); -- Count indexes with PARALLEL > 1 before SELECT COUNT(*) INTO v_parallel_before FROM dba_indexes WHERE owner = v_owner AND NVL(DEGREE, '1') NOT IN ('1', 'DEFAULT'); DBMS_OUTPUT.PUT_LINE('Unusable indexes before rebuild: ' || v_unusable_before); DBMS_OUTPUT.PUT_LINE('Indexes with parallelism > 1 before rebuild: ' || v_parallel_before); -- Rebuild global unusable indexes FOR rec IN ( SELECT index_name, owner FROM dba_indexes WHERE status = 'UNUSABLE' AND owner = v_owner ) LOOP v_sql := 'ALTER INDEX "' || rec.owner || '"."' || rec.index_name || '" REBUILD ONLINE PARALLEL 4'; DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql); BEGIN EXECUTE IMMEDIATE v_sql; EXECUTE IMMEDIATE 'ALTER INDEX "' || rec.owner || '"."' || rec.index_name || '" NOPARALLEL'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed: ' || v_sql || ' - ' || SQLERRM); END; END LOOP; -- Rebuild unusable index partitions FOR rec IN ( SELECT index_name, index_owner, partition_name FROM dba_ind_partitions WHERE status = 'UNUSABLE' AND index_owner = v_owner ) LOOP v_sql := 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name || '" REBUILD PARTITION "' || rec.partition_name || '" ONLINE PARALLEL 4'; DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql); BEGIN EXECUTE IMMEDIATE v_sql; EXECUTE IMMEDIATE 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name || '" NOPARALLEL'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed: ' || v_sql || ' - ' || SQLERRM); END; END LOOP; -- Rebuild unusable index subpartitions FOR rec IN ( SELECT index_name, index_owner, subpartition_name FROM dba_ind_subpartitions WHERE status = 'UNUSABLE' AND index_owner = v_owner ) LOOP v_sql := 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name || '" REBUILD SUBPARTITION "' || rec.subpartition_name || '" ONLINE PARALLEL 4'; DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql); BEGIN EXECUTE IMMEDIATE v_sql; EXECUTE IMMEDIATE 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name || '" NOPARALLEL'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed: ' || v_sql || ' - ' || SQLERRM); END; END LOOP; -- Count unusable indexes after SELECT COUNT(*) INTO v_unusable_after FROM ( SELECT 1 FROM dba_indexes WHERE owner = v_owner AND status = 'UNUSABLE' UNION ALL SELECT 1 FROM dba_ind_partitions WHERE index_owner = v_owner AND status = 'UNUSABLE' UNION ALL SELECT 1 FROM dba_ind_subpartitions WHERE index_owner = v_owner AND status = 'UNUSABLE' ); -- Count indexes with PARALLEL > 1 after SELECT COUNT(*) INTO v_parallel_after FROM dba_indexes WHERE owner = v_owner AND NVL(DEGREE, '1') NOT IN ('1', 'DEFAULT'); DBMS_OUTPUT.PUT_LINE('Unusable indexes after rebuild: ' || v_unusable_after); DBMS_OUTPUT.PUT_LINE('Indexes with parallelism > 1 after reset: ' || v_parallel_after); END; / SPOOL OFF