各位用户为了找寻关于Oracle重建索引Shell脚本、SQL脚本分享的资料费劲了很多周折。这里教程网为您整理了关于Oracle重建索引Shell脚本、SQL脚本分享的相关资料，仅供查阅，以下为您介绍关于Oracle重建索引Shell脚本、SQL脚本分享的详细内容

索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的DML的情形下会产生相应的碎片，以及B树高度会发生相应变化，因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4，已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章：Oracle 重建索引的必要性。尽管如此重建索引还是有必要的，只是不建议定期。本文给出了重建索引的脚本供大家参考。

1、重建索引shell脚本

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 robin@SZDB:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh # +-------------------------------------------------------+ # +  Rebulid unblanced indices             | # +  Author : Leshami                  | # +  Parameter : No                   | # +-------------------------------------------------------+   #!/bin/bash # -------------------- # Define variable # --------------------   if [ -f ~/.bash_profile ]; then . ~/.bash_profile fi   DT=`date +%Y%m%d`;       export DT RETENTION=1 LOG_DIR=/tmp LOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.log DBA=Leshami@12306.cn   # ------------------------------------ # Loop all instance in current server # ------------------------------------- echo "Current date and time is : `/bin/date`">>${LOG}   for db in `ps -ef | grep pmon | grep -v grep |grep -v asm |awk '{print $8}'|cut -c 10-` do   echo "$db"   export ORACLE_SID=$db   echo "Current DB is $db" >>${LOG}   echo "===============================================">>${LOG}   $ORACLE_HOME/bin/sqlplus -S /nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql>>${LOG} done;   echo "End of rebuilding index for all instance at : `/bin/date`">>${LOG} # ------------------------------------- # Check log file # ------------------------------------- status=`grep "ORA-" ${LOG}` if [ -z $status ];then   mail -s "Succeeded rebuilding indices on `hostname` !!!" ${DBA} <${LOG} else   mail -s "Failed rebuilding indices on `hostname` !!!" ${DBA} <${LOG} fi   # ------------------------------------------------ # Removing files older than $RETENTION parameter # ------------------------------------------------   find ${LOG_DIR} -name "rebuild_unb*" -mtime +$RETENTION -exec rm {} ;   exit

2、重建索引调用的SQL脚本

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 robin@SZDB:~/dba_scripts/custom/sql> more rebuild_unbalanced_indices.sql conn / as sysdba set serveroutput on; DECLARE   resource_busy        EXCEPTION;   PRAGMA EXCEPTION_INIT (resource_busy, -54);   c_max_trial    CONSTANT PLS_INTEGER := 10;   c_trial_interval  CONSTANT PLS_INTEGER := 1;   pmaxheight     CONSTANT INTEGER := 3;   pmaxleafsdeleted  CONSTANT INTEGER := 20;     CURSOR csrindexstats   IS    SELECT NAME,        height,        lf_rows AS leafrows,        del_lf_rows AS leafrowsdeleted     FROM index_stats;     vindexstats         csrindexstats%ROWTYPE;     CURSOR csrglobalindexes   IS    SELECT owner,index_name, tablespace_name     FROM dba_indexes     WHERE partitioned = 'NO'     AND owner IN ('GX_ADMIN');     CURSOR csrlocalindexes   IS    SELECT index_owner,index_name, partition_name, tablespace_name     FROM dba_ind_partitions     WHERE status = 'USABLE'     AND index_owner IN ('GX_ADMIN');     trial            PLS_INTEGER;   vcount           INTEGER := 0; BEGIN   trial := 0;     /* Global indexes */   FOR vindexrec IN csrglobalindexes   LOOP    EXECUTE IMMEDIATE      'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure';      OPEN csrindexstats;      FETCH csrindexstats INTO vindexstats;      IF csrindexstats%FOUND    THEN      IF  (vindexstats.height > pmaxheight)       OR (  vindexstats.leafrows > 0         AND vindexstats.leafrowsdeleted > 0         AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >             pmaxleafsdeleted)      THEN       vcount := vcount + 1;       DBMS_OUTPUT.PUT_LINE (         'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...');         <<alter_index>>       BEGIN         EXECUTE IMMEDIATE            'alter index '          || vindexrec.owner ||'.'          || vindexrec.index_name          || ' rebuild'          || ' parallel nologging compute statistics'          || ' tablespace '          || vindexrec.tablespace_name;       EXCEPTION         WHEN resource_busy OR TIMEOUT_ON_RESOURCE         THEN          DBMS_OUTPUT.PUT_LINE (            'alter index - busy and wait for 1 sec');          DBMS_LOCK.sleep (c_trial_interval);            IF trial <= c_max_trial          THEN            GOTO alter_index;          ELSE            DBMS_OUTPUT.PUT_LINE (               'alter index busy and waited - quit after '             || TO_CHAR (c_max_trial)             || ' trials');            RAISE;          END IF;         WHEN OTHERS         THEN          DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM);          RAISE;       END;      END IF;    END IF;      CLOSE csrindexstats;   END LOOP;     DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount));   vcount := 0;   trial := 0;     /* Local indexes */   FOR vindexrec IN csrlocalindexes   LOOP    EXECUTE IMMEDIATE       'analyze index '      || vindexrec.index_owner||'.'      || vindexrec.index_name      || ' partition ('      || vindexrec.partition_name      || ') validate structure';      OPEN csrindexstats;      FETCH csrindexstats INTO vindexstats;      IF csrindexstats%FOUND    THEN      IF  (vindexstats.height > pmaxheight)       OR (  vindexstats.leafrows > 0         AND vindexstats.leafrowsdeleted > 0         AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >             pmaxleafsdeleted)      THEN       vcount := vcount + 1;       DBMS_OUTPUT.PUT_LINE (         'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...');         <<alter_partitioned_index>>       BEGIN         EXECUTE IMMEDIATE            'alter index '          || vindexrec.index_owner||'.'          || vindexrec.index_name          || ' rebuild'          || ' partition '          || vindexrec.partition_name          || ' parallel nologging compute statistics'          || ' tablespace '          || vindexrec.tablespace_name;       EXCEPTION         WHEN resource_busy OR TIMEOUT_ON_RESOURCE         THEN          DBMS_OUTPUT.PUT_LINE (            'alter partitioned index - busy and wait for 1 sec');          DBMS_LOCK.sleep (c_trial_interval);            IF trial <= c_max_trial          THEN            GOTO alter_partitioned_index;          ELSE            DBMS_OUTPUT.PUT_LINE (               'alter partitioned index busy and waited - quit after '             || TO_CHAR (c_max_trial)             || ' trials');            RAISE;          END IF;         WHEN OTHERS         THEN          DBMS_OUTPUT.PUT_LINE (            'alter partitioned index err ' || SQLERRM);          RAISE;       END;      END IF;    END IF;      CLOSE csrindexstats;   END LOOP;     DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount)); END; / exit;

3、输入日志样本

? 1 2 3 4 5 6 7 8 Current date and time is : Sun Apr 20 02:00:02 HKT 2014 Current DB is SYBO2 =============================================== Rebuilding index GX_ADMIN.SYN_OUT_DATA_TBL_PK... Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_PARENT_REF... Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_CHILD_REF... Rebuilding index GX_ADMIN.PK_TRADE_BROKER_TBL... Rebuilding index GX_ADMIN.IDX_TDBK_INPUT_DATE...   ................

4、后记

a、如果同一台服务器上有多个实例，且每个实例有相同的schema，此脚本会轮巡所有实例并根据analyze结果来rebuild。 a、大家应根据需要作相应调整，如脚本的路径信息等。 b、需要修改相应的schema name。 d、可根据系统环境调整相应的并行度。