Index rebuild script

This is Simple script to automate the Index rebuild in your environment....
in this script we are capturing parallel degree before rebuilding the indexes and will reset to the existing once the index rebuild is done...



SET FEEDBACK OFF
set serveroutput on size unlimited
set pagesize 4000
set heading off
set linesize 200
DECLARE
 v_Count   INT;
    BEGIN
        SELECT 1
          INTO v_Count
          FROM User_Tables
         WHERE Table_Name = 'INDEX_REBUILD_LIST';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            BEGIN
                EXECUTE IMMEDIATE 'CREATE TABLE INDEX_REBUILD_LIST
                                   (OWNER               VARCHAR2(20),
                                    INDEX_NAME          VARCHAR2(40),
                                    DEGREE              NUMBER)';
 EXCEPTION
                WHEN OTHERS THEN
                    RAISE_APPLICATION_ERROR(-20001, 'ERROR in Creating USER_DROP_TAB table' || CHR(10) || SQLERRM);
            END;
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20002, 'ERROR - ' || CHR(10) || SQLERRM);
    END;
/


INSERT INTO INDEX_REBUILD_LIST select owner,index_name,degree from dba_indexes where owner = 'User_Schema';

select count(*) from INDEX_REBUILD_LIST;

--select * from INDEX_REBUILD_LIST;

Prompt "******Running Index Rebuilds with Parallel 4 ******"
spool exec_index_rebuilds_User_Schema.sql
select 'alter index '||owner||'.'||index_name||' rebuild nologging  parallel 4;' from INDEX_REBUILD_LIST;
spool off
prompt "******Setting Parallel to Original******";

spool exec_index_rebuild_reset_parallel.sql
select 'alter index '||owner||'.'||index_name||'  parallel '||degree||';' from INDEX_REBUILD_LIST;
spool off

drop table INDEX_REBUILD_LIST;

--below steps are to perform the operations
@exec_index_rebuilds_User_schema.sql
@exec_index_rebuild_reset_parallel.sql


exit;



Hope this Helps....