Shulou(Shulou.com)06/01 Report--

This article is about how a single script monitors the tablespace utilization of all instances on the host. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Store the output of ps-ef | grep pmon with the help of a file

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

Oracle 8922 10 Dec 17? 10:48 ora_pmon_spw2005p

Oracle 13535 10 Dec 17? 10:00 ora_pmon_mstrmd1p

Oracle 28599 10 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 10 Dec 17? 10:57 ora_pmon_spw2003p

Oracle 16046 1 0 Dec 17? 12:32 ora_pmon_crs2007t

Oracle 5002 10 Dec 19? 10:04 ora_pmon_smart3p

Shell script

1 iterate through the file and take out the sid

2 iterate through the sid list, execute the sql script to check the tablespace respectively, and send the results to the relevant personnel

#! / 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 > $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" * * @ * * .com < $tmp_file

Fi

Done

Corresponding sql script

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'

)

/

Thank you for reading! This is the end of the article on "how a single script monitors the tablespace utilization of all instances on the host". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.