單個指令碼監控主機上所有例項的表空間利用率

藉助一個檔案，存放ps –ef | grep pmon的輸出

[test]:/orasw/dba>more /orasw/dba/scripts/ db_running.log 

  oracle  8922     1  0  Dec 17  ?        10:48 ora_pmon_spw2005p

  oracle 13535     1  0  Dec 17  ?        10:00 ora_pmon_mstrmd1p

  oracle 28599     1  0  Dec 19  ?        10:39 ora_pmon_tsr2008p

  oracle  7561     1  0  Dec 17  ?        11:57 ora_pmon_asa2p

  oracle  6756     1  0  Dec 17  ?        11:53 ora_pmon_tsr2009p

  oracle 12041     1  0  Dec 17  ?        11:01 ora_pmon_spw06p

  oracle  6737     1  0  Dec 17  ?        10:57 ora_pmon_spw2003p

  oracle 16046     1  0  Dec 17  ?        12:32 ora_pmon_crs2007t

  oracle  5002     1  0  Dec 19  ?        10:04 ora_pmon_smart3p

 

shell指令碼

1 遍歷該檔案，將sid取出

2 遍歷sid 列表，分別執行檢查表空間sql指令碼，並將結果傳送給相關人員

#!/bin/ksh

db_name=`cat /orasw/dba/scripts/ db_running.log|awk 'BEGIN { FS = "_" } ; { print $3 }'|sed '/^$/d'`

echo $db_name

for k in $db_name

do

h=`ps -ef | grep pmon | grep -c $k`

echo $k

ORACLE_SID=$k; export ORACLE_SID

ORAENV_ASK=NO

. oraenv

#. oraenv

export ORACLE_HOME

# Set threshhold limit for tablespace to send alert

#

export threshold=70

export script_dir=/orasw/dba/scripts/

rm -rf  $script_dir/${k}_ts.rpt

rm -rf $script_dir/${k}_chk_ts.out

cut_of_pt=$threshold

tmp_file=$script_dir/${k}_chk_ts.out

ts_stat_rpt=$script_dir/${k}_ts.rpt

 

sqlplus -s '/ as sysdba' @$script_dir/chk_ts.sql >> $ts_stat_rpt <<-EOF

exit

EOF

echo "TABLESPACE_NAME      PCT_USED(%)" > $tmp_file

echo "-----------------------------" >> $tmp_file

cat $ts_stat_rpt | awk '{

                if (int($2) > int("'$cut_of_pt'"))

                                print $0

                }' >> $tmp_file

 

echo "----------------------------------" >> $ts_stat_rpt

if test -s $tmp_file

then

        subject="Below TableSpaces crossed threshold limits of $cut_of_pt in $k. Please take immediate action."

        echo $subject

mailx -s "$subject" < $tmp_file

fi

done

 

相應sql指令碼

bash-3.2$ more chk_ts.sql

set lines 120

set pages 100

set echo off

set feedback off

set head off

col PCT_USED format a10

col tablespace_name   format a20

(select t.tablespace_name tablespace_name,TO_CHAR((1 - DECODE(tsf.bytes, NULL, tsa.bytes, tsf.bytes) / tsa.bytes) * 100, '990') PCT_USED

from sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf

where t.tablespace_name = tsa.tablespace_name and t.tablespace_name = tsf.tablespace_name (+)

)

union

(SELECT D.TABLESPACE_NAME TABLESPACE_NAME, TO_CHAR(DECODE(A.SUM_ALLOCATE, NULL, 0, NVL(T.SUM_USED, 0) / A.SUM_ALLOCATE * 100), '990') PCT_USED

FROM DBA_TABLESPACES D,

   (SELECT TABLESPACE_NAME, SUM(BYTES) SUM_ALLOCATE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A,

   (SELECT TABLESPACE_NAME, SUM(BYTES_CACHED) SUM_USED FROM V$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME) T

WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)

AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)

AND D.EXTENT_MANAGEMENT = 'LOCAL' AND D.CONTENTS = 'TEMPORARY'

)

/