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}
