THEN
           'alter partitioned index err ' || SQLERRM);
if [ -f ~/.bash_profile ]; then
    FROM dba_ind_partitions
  ................
     || ') validate structure';
   SELECT owner,index_name, tablespace_name
              'alter index busy and waited - quit after '
# Removing files older than $RETENTION parameter
         || ' tablespace '

set serveroutput on;
  FOR vindexrec IN csrlocalindexes

     IF  (vindexstats.height > pmaxheight)
echo "Current date and time is : `/bin/date`">>${LOG}

         || ' parallel nologging compute statistics'
d、可根据系统环境调整相应的并行度。
# --------------------

  CURSOR csrglobalindexes
status=`grep "ORA-" ${LOG}`
这篇文章主要介绍了Oracle重建索引Shell脚本、SQL脚本分享,索引是提高数据库查询性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以参考下
     'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure';
  c_max_trial    CONSTANT PLS_INTEGER := 10;
       lf_rows AS leafrows,
         DBMS_LOCK.sleep (c_trial_interval);
  PRAGMA EXCEPTION_INIT (resource_busy, -54);
  trial := 0;

           'alter index '
Current date and time is : Sun Apr 20 02:00:02 HKT 2014

  trial            PLS_INTEGER;

        'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...');
   IF csrindexstats%FOUND
         DBMS_OUTPUT.PUT_LINE (
         || ' partition '
  mail -s "Succeeded rebuilding indices on `hostname` !!!" ${DBA} <${LOG}
2、重建索引调用的SQL脚本
        AND vindexstats.leafrowsdeleted > 0
# +  Rebulid unblanced indices             |

         ELSE

         END IF;

BEGIN
     || vindexrec.partition_name
# +-------------------------------------------------------+
fi
do

echo "End of rebuilding index for all instance at : `/bin/date`">>${LOG}
           'alter index - busy and wait for 1 sec');

# +  Parameter : No                   |
      EXCEPTION
      BEGIN
  $ORACLE_HOME/bin/sqlplus -S /nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql>>${LOG}
Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_CHILD_REF...
      OR (  vindexstats.leafrows > 0
  DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));
  CURSOR csrindexstats
           RAISE;
  FOR vindexrec IN csrglobalindexes
  IS

         DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM);
         || vindexrec.partition_name
     THEN
  c_trial_interval  CONSTANT PLS_INTEGER := 1;
       height,
         || ' rebuild'

           'alter index '
  vcount := 0;
        THEN
         END IF;
         THEN

  DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount));
        WHEN resource_busy OR TIMEOUT_ON_RESOURCE
   END IF;
            pmaxleafsdeleted)

      <<alter_partitioned_index>>
fi
Current DB is SYBO2 ===============================================
done;
      EXCEPTION
Rebuilding index GX_ADMIN.PK_TRADE_BROKER_TBL...
LOG_DIR=/tmp
a、大家应根据需要作相应调整，如脚本的路径信息等。
    FROM dba_indexes
      DBMS_OUTPUT.PUT_LINE (
END;
robin@SZDB:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh
    AND index_owner IN ('GX_ADMIN');
         THEN
  IS
. ~/.bash_profile
    WHERE status = 'USABLE'
else
            || TO_CHAR (c_max_trial)
3、输入日志样本
# ------------------------------------
        THEN
  export ORACLE_SID=$db
   SELECT NAME,
  echo "$db"
      BEGIN
            || ' trials');
  resource_busy        EXCEPTION;
        THEN
   END IF;
b、需要修改相应的schema name。
conn / as sysdba

      OR (  vindexstats.leafrows > 0
  END LOOP;
# ------------------------------------------------
        THEN
# -------------------------------------
         || vindexrec.tablespace_name;
   OPEN csrindexstats;
# --------------------
         || vindexrec.index_name
LOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.log
  /* Global indexes */
  LOOP
exit
        AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >
         DBMS_LOCK.sleep (c_trial_interval);
# Loop all instance in current server
  mail -s "Failed rebuilding indices on `hostname` !!!" ${DBA} <${LOG}
         IF trial <= c_max_trial
           DBMS_OUTPUT.PUT_LINE (
           GOTO alter_partitioned_index;
# -------------------------------------
  IS
         RAISE;
exit;
   OPEN csrindexstats;
  LOOP
            || ' trials');
        AND vindexstats.leafrowsdeleted > 0
           GOTO alter_index;
         || ' parallel nologging compute statistics'
Rebuilding index GX_ADMIN.IDX_TDBK_INPUT_DATE...
   SELECT index_owner,index_name, partition_name, tablespace_name
           RAISE;
  echo "Current DB is $db" >>${LOG}

      END;
     END IF;
   THEN
4、后记
  END LOOP;
      <<alter_index>>
   FETCH csrindexstats INTO vindexstats;
         DBMS_OUTPUT.PUT_LINE (
  echo "===============================================">>${LOG}
        EXECUTE IMMEDIATE

        EXECUTE IMMEDIATE
         || vindexrec.index_owner||'.'

1、重建索引shell脚本
            pmaxleafsdeleted)
        WHEN resource_busy OR TIMEOUT_ON_RESOURCE
  CURSOR csrlocalindexes
# +-------------------------------------------------------+
  vcount           INTEGER := 0;
# ------------------------------------------------
         DBMS_OUTPUT.PUT_LINE (
        AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >
      'analyze index '
DECLARE
  pmaxleafsdeleted  CONSTANT INTEGER := 20;
   THEN
# Define variable
      END;
   IF csrindexstats%FOUND

        WHEN OTHERS

     || vindexrec.index_name

         ELSE
         || vindexrec.tablespace_name;
   FETCH csrindexstats INTO vindexstats;
   CLOSE csrindexstats;
      DBMS_OUTPUT.PUT_LINE (
  trial := 0;
     || vindexrec.index_owner||'.'
        'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...');


   EXECUTE IMMEDIATE
         || vindexrec.owner ||'.'
if [ -z $status ];then
# Check log file
robin@SZDB:~/dba_scripts/custom/sql> more rebuild_unbalanced_indices.sql
      vcount := vcount + 1;
/
    AND owner IN ('GX_ADMIN');
find ${LOG_DIR} -name "rebuild_unb*" -mtime +$RETENTION -exec rm {} \;
  pmaxheight     CONSTANT INTEGER := 3;

      vcount := vcount + 1;

# -------------------------------------
# +  Author : Leshami                  |

           DBMS_OUTPUT.PUT_LINE (
[email protected]
        WHEN OTHERS
  vindexstats         csrindexstats%ROWTYPE;
Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_PARENT_REF...
     IF  (vindexstats.height > pmaxheight)
           'alter partitioned index - busy and wait for 1 sec');
DT=`date +%Y%m%d`;       export DT
              'alter partitioned index busy and waited - quit after '
索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的DML的情形下会产生相应的碎片，以及B树高度会发生相应变化，因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4，已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章：Oracle 重建索引的必要性。尽管如此重建索引还是有必要的，只是不建议定期。本文给出了重建索引的脚本供大家参考。
         || ' tablespace '

RETENTION=1
            || TO_CHAR (c_max_trial)
   EXECUTE IMMEDIATE

    FROM index_stats;
       del_lf_rows AS leafrowsdeleted
         || ' rebuild'
         IF trial <= c_max_trial
         RAISE;
#!/bin/bash
for db in `ps -ef | grep pmon | grep -v grep |grep -v asm |awk '{print $8}'|cut -c 10-`
     || ' partition ('
  /* Local indexes */

         || vindexrec.index_name
Rebuilding index GX_ADMIN.SYN_OUT_DATA_TBL_PK...

   CLOSE csrindexstats;
a、如果同一台服务器上有多个实例，且每个实例有相同的schema，此脚本会轮巡所有实例并根据analyze结果来rebuild。
     END IF;
    WHERE partitioned = 'NO'