1、重建索引shell腳本

 

view sourceprint?
01.
robin@SZDB:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh
02.
# +-------------------------------------------------------+
03.
# + Rebulid unblanced indices |
04.
# + Author : Leshami |
05.
# + Parameter : No |
06.
# + Blog : HTTP://blog.csdn.net/leshami |
07.
# +-------------------------------------------------------+
08.

 

09.
#!/bin/bash
10.
# --------------------
11.
# Define variable
12.
# --------------------
13.

 

14.
if [ -f ~/.bash_profile ]; then
15.
. ~/.bash_profile
16.
fi
17.

 

18.
DT=`date +%Y%m%d`; export DT
19.
RETENTION=1
20.
LOG_DIR=/tmp
21.
LOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.log
22.
DBA=Leshami@12306.cn
23.

 

24.
# ------------------------------------
25.
# Loop all instance in current server
26.
# -------------------------------------
27.
echo "Current date and time is : `/bin/date`">>${LOG}
28.

 

29.
for db in `ps -ef | grep pmon | grep -v grep |grep -v asm |awk '{print $8}'|cut -c 10-`
30.
do
31.
echo "$db"
32.
export ORACLE_SID=$db
33.
echo "Current DB is $db" >>${LOG}
34.
echo "===============================================">>${LOG}
35.
$ORACLE_HOME/bin/sqlplus -S /nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql>>${LOG}
36.
done;
37.

 

38.
echo "End of rebuilding index for all instance at : `/bin/date`">>${LOG}
39.
# -------------------------------------
40.
# Check log file
41.
# -------------------------------------
42.
status=`grep "ORA-" ${LOG}`
43.
if [ -z $status ];then
44.
mail -s "Succeeded rebuilding indices on `hostname` !!!" ${DBA} <${LOG}
45.
else
46.
mail -s "Failed rebuilding indices on `hostname` !!!" ${DBA} <${LOG}
47.
fi
48.

 

49.
# ------------------------------------------------
50.
# Removing files older than $RETENTION parameter
51.
# ------------------------------------------------
52.

 

53.
find ${LOG_DIR} -name "rebuild_unb*" -mtime +$RETENTION -exec rm {} ;
54.

 

55.
exit

 

2、重建索引調用的SQL腳本

 

view sourceprint?
001.
robin@SZDB:~/dba_scripts/custom/sql> more rebuild_unbalanced_indices.sql
002.
conn / as sysdba
003.
set serveroutput on;
004.
DECLARE
005.
resource_busy EXCEPTION;
006.
PRAGMA EXCEPTION_INIT (resource_busy, -54);
007.
c_max_trial CONSTANT PLS_INTEGER := 10;
008.
c_trial_interval CONSTANT PLS_INTEGER := 1;
009.
pmaxheight CONSTANT INTEGER := 3;
010.
pmaxleafsdeleted CONSTANT INTEGER := 20;
011.

 

012.
CURSOR csrindexstats
013.
IS
014.
SELECT NAME,
015.
height,
016.
lf_rows AS leafrows,
017.
del_lf_rows AS leafrowsdeleted
018.
FROM index_stats;
019.

 

020.
vindexstats csrindexstats%ROWTYPE;
021.

 

022.
CURSOR csrglobalindexes
023.
IS
024.
SELECT owner,index_name, tablespace_name
025.
FROM dba_indexes
026.
WHERE partitioned = 'NO'
027.
AND owner IN ('GX_ADMIN');
028.

 

029.
CURSOR csrlocalindexes
030.
IS
031.
SELECT index_owner,index_name, partition_name, tablespace_name
032.
FROM dba_ind_partitions
033.
WHERE status = 'USABLE'
034.
AND index_owner IN ('GX_ADMIN');
035.

 

036.
trial PLS_INTEGER;
037.
vcount INTEGER := 0;
038.
BEGIN
039.
trial := 0;
040.

 

041.
/* Global indexes */
042.
FOR vindexrec IN csrglobalindexes
043.
LOOP
044.
EXECUTE IMMEDIATE
045.
'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure';
046.

 

047.
OPEN csrindexstats;
048.

 

049.
FETCH csrindexstats INTO vindexstats;
050.

 

051.
IF csrindexstats%FOUND
052.
THEN
053.
IF (vindexstats.height > pmaxheight)
054.
OR ( vindexstats.leafrows > 0
055.
AND vindexstats.leafrowsdeleted > 0
056.
AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >
057.
pmaxleafsdeleted)
058.
THEN
059.
vcount := vcount + 1;
060.
DBMS_OUTPUT.PUT_LINE (
061.
'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...');
062.

 

063.
<<alter_index>>
064.
BEGIN
065.
EXECUTE IMMEDIATE
066.
'alter index '
067.
|| vindexrec.owner ||'.'
068.
|| vindexrec.index_name
069.
|| ' rebuild'
070.
|| ' parallel nologging compute statistics'
071.
|| ' tablespace '
072.
|| vindexrec.tablespace_name;
073.
EXCEPTION
074.
WHEN resource_busy OR TIMEOUT_ON_RESOURCE
075.
THEN
076.
DBMS_OUTPUT.PUT_LINE (
077.
'alter index - busy and wait for 1 sec');
078.
DBMS_LOCK.sleep (c_trial_interval);
079.

 

080.
IF trial <= c_max_trial
081.
THEN
082.
GOTO alter_index;
083.
ELSE
084.
DBMS_OUTPUT.PUT_LINE (
085.
'alter index busy and waited - quit after '
086.
|| TO_CHAR (c_max_trial)
087.
|| ' trials');
088.
RAISE;
089.
END IF;
090.
WHEN OTHERS
091.
THEN
092.
DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM);
093.
RAISE;
094.
END;
095.
END IF;
096.
END IF;
097.

 

098.
CLOSE csrindexstats;
099.
END LOOP;
100.

 

101.
DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount));
102.
vcount := 0;
103.
trial := 0;
104.

 

105.
/* Local indexes */

106.
FOR vindexrec IN csrlocalindexes
107.
LOOP
108.
EXECUTE IMMEDIATE
109.
'analyze index '
110.
|| vindexrec.index_owner||'.'
111.
|| vindexrec.index_name
112.
|| ' partition ('
113.
|| vindexrec.partition_name
114.
|| ') validate structure';
115.

116.
OPEN csrindexstats;
117.

118.
FETCH csrindexstats INTO vindexstats;
119.

120.
IF csrindexstats%FOUND
121.
THEN
122.
IF (vindexstats.height > pmaxheight)
123.
OR ( vindexstats.leafrows > 0
124.
AND vindexstats.leafrowsdeleted > 0
125.
AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >
126.
pmaxleafsdeleted)
127.
THEN
128.
vcount := vcount + 1;
129.
DBMS_OUTPUT.PUT_LINE (
130.
'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...');
131.

132.
<<alter_partitioned_index>>
133.
BEGIN
134.
EXECUTE IMMEDIATE
135.
'alter index '
136.
|| vindexrec.index_owner||'.'
137.
|| vindexrec.index_name
138.
|| ' rebuild'
139.
|| ' partition '
140.
|| vindexrec.partition_name
141.
|| ' parallel nologging compute statistics'
142.
|| ' tablespace '
143.
|| vindexrec.tablespace_name;
144.
EXCEPTION
145.
WHEN resource_busy OR TIMEOUT_ON_RESOURCE
146.
THEN
147.
DBMS_OUTPUT.PUT_LINE (
148.
'alter partitioned index - busy and wait for 1 sec');
149.
DBMS_LOCK.sleep (c_trial_interval);
150.

151.
IF trial <= c_max_trial
152.
THEN
153.
GOTO alter_partitioned_index;
154.
ELSE
155.
DBMS_OUTPUT.PUT_LINE (
156.
'alter partitioned index busy and waited - quit after '
157.
|| TO_CHAR (c_max_trial)
158.
|| ' trials');
159.
RAISE;
160.
END IF;
161.
WHEN OTHERS
162.
THEN
163.
DBMS_OUTPUT.PUT_LINE (
164.
'alter partitioned index err ' || SQLERRM);
165.
RAISE;
166.
END;
167.
END IF;
168.
END IF;
169.

170.
CLOSE csrindexstats;
171.
END LOOP;
172.

173.
DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));
174.
END;
175.
/
176.
exit;

3、輸入日誌樣本
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、可根據系統內容調整相應的並行度。
創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(0) 人氣()