Rebuild Indexes Online. Generate script per schema

Non Partitioned indexes

--gen_rebuild_indexes_online.sql

COL sql_cmd FOR A120

SET LINESIZE 140

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

SET ECHO OFF

SET NEWPAGE NONE

SET SHOW OFF

SET TRIMSPOOL ON

spool rebuild_indexes_online.sql

PROMPT COL sql_cmd FOR A120

PROMPT SET LINESIZE 140

PROMPT SET PAGESIZE 0

PROMPT SET HEADING OFF

PROMPT SET FEEDBACK OFF

PROMPT SET VERIFY OFF

PROMPT SET ECHO OFF

PROMPT SET NEWPAGE NONE

PROMPT SET SHOW OFF

PROMPT SET TRIMSPOOL ON

SELECT 'PROMPT Handle '||table_name||'.'||index_name||CHR(10)|| 'ALTER INDEX '||index_name||' REBUILD ONLINE;' as sql_cmd

FROM 

(

SELECT index_name, table_name FROM USER_INDEXES WHERE index_name IN

 (

 SELECT index_name 

   FROM USER_INDEXES WHERE index_type = 'NORMAL' AND partitioned='NO'

 MINUS

 (

          SELECT constraint_name FROM USER_CONSTRAINTS WHERE constraint_type = 'U' AND deferrable = 'DEFERRABLE'

   UNION  SELECT 'SFI_CUSTOMER_PROFILE_PK' FROM DUAL

   UNION  SELECT 'SCP_MSISDN_IDX' FROM DUAL

  )

 )

ORDER BY table_name

);

--Check status

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'PROMPT Check Non-Pertitioned Indexes Status After Non-Pertitioned Indexes Rebuild' FROM DUAL;

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'SELECT table_name, index_name, status FROM USER_INDEXES WHERE partitioned=''NO'' AND status NOT IN ( ''VALID'', ''N/A'');' FROM DUAL;

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'PROMPT Finished Non-Pertitioned Indexes Rebuild' FROM DUAL;

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'EXIT;' FROM DUAL;

spool off;

EXIT;

Partitioned indexes

--gen_rebuild_part_indexes_online.sql

COL sql_cmd FOR A120

SET LINESIZE 140

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

SET ECHO OFF

SET NEWPAGE NONE

SET SHOW OFF

SET TRIMSPOOL ON

spool rebuild_part_indexes_online.sql

PROMPT COL sql_cmd FOR A120

PROMPT SET LINESIZE 140

PROMPT SET PAGESIZE 0

PROMPT SET HEADING OFF

PROMPT SET FEEDBACK OFF

PROMPT SET VERIFY OFF

PROMPT SET ECHO OFF

PROMPT SET NEWPAGE NONE

PROMPT SET SHOW OFF

PROMPT SET TRIMSPOOL ON

SELECT 'PROMPT Handle '||table_name||'.'||index_name||' partition '||partition_name||CHR(10)|| 'ALTER INDEX '||index_name||' REBUILD PARTITION '||partition_name||' ONLINE;' as sql_cmd

FROM 

(

SELECT table_name, UI.index_name, UIP.partition_name  

  FROM USER_INDEXES UI,

       USER_IND_PARTITIONS UIP

 WHERE UI.index_name = UIP.index_name

   AND UI.index_name IN

 (

 SELECT index_name FROM USER_INDEXES WHERE index_type = 'NORMAL'  AND partitioned='YES'

 MINUS

 (        SELECT constraint_name FROM USER_CONSTRAINTS WHERE constraint_type = 'U' AND deferrable = 'DEFERRABLE'

   UNION  SELECT 'SFI_CUSTOMER_PROFILE_PK' FROM DUAL

   UNION  SELECT 'SCP_MSISDN_IDX' FROM DUAL

  )

 )

ORDER BY table_name

);

--Check status

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'PROMPT Check Pertitioned Indexes Status After Pertitioned Indexes Rebuild' FROM DUAL;

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'SELECT UI.table_name, UI.index_name, UIP.partition_name, UIP.status 

        FROM USER_INDEXES UI, USER_IND_PARTITIONS UIP

WHERE UI.partitioned=''YES'' AND UIP.index_name = UI.index_name AND UIP.status NOT IN ( ''USABLE'')

UNION ALL 

SELECT UI.table_name, UI.index_name,NULL, UI.status 

        FROM USER_INDEXES UI

WHERE UI.partitioned=''YES'' AND UI.status NOT IN ( ''VALID'', ''N/A'')

;' FROM DUAL;

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'PROMPT Finished Non-Pertitioned Indexes Rebuild' FROM DUAL;

SELECT 'PROMPT ======================' FROM DUAL;

SELECT 'EXIT;' FROM DUAL;

spool off;

EXIT;

To manually execute generated files:

@rebuild_indexes_online.sql

@rebuild_part_indexes_online.sql

Bash script

#!/bin/bash

#. /etc/sh/orash/oracle_login.sh igt

#. ~oracle/.set_oracle_env

USER_NAME=$1

USER_PASS=$2

ORA_INST=$3

WORK_DIR=`pwd`

LOG_NAME='rebuild_indexes.log'

LOG_FILE=${WORK_DIR}/${LOG_NAME}

RUN_DATE=`date "+%Y%m%d"_"%H%M"`

echo "Start Indexes Rebuild at $RUN_DATE" >> ${LOG_FILE}

echo "Schema Details: ${USER_NAME}/${USER_PASS}" >> ${LOG_FILE}

sqlplus -s ${USER_NAME}/${USER_PASS}@${ORA_INST} @gen_rebuild_indexes_online.sql

sqlplus -s ${USER_NAME}/${USER_PASS}@${ORA_INST} @gen_rebuild_part_indexes_online.sql

sqlplus -s ${USER_NAME}/${USER_PASS}@${ORA_INST} @rebuild_indexes_online.sql

sqlplus -s ${USER_NAME}/${USER_PASS}@${ORA_INST} @rebuild_part_indexes_online.sql

echo "Finished Indexes Rebuild at $RUN_DATE" >> ${LOG_FILE}

echo "" >> ${LOG_FILE}

Bash wrapper script

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

WORK_DIR=`pwd`

USER_NAME=SOME_USER

USER_PASS=SOME_PASS

ORA_INST=igt

./main_rebuild_indexes.sh ${USER_NAME} ${USER_PASS} ${ORA_INST}