Oracle表領域の使用率表示

DB中の全表領域の利用率をSQLで表示。cron等のバッチで実行してメールでお知らせまでやってるので、全3ファイルから。

get_tablespace_name.sql	全tablespace名の取得
get_tablespace_df.sql	指定tablespaceの利用率取得
get_tablespace_df.sh	上記SQLを実行するスクリプト

get_tablespace_name.sql

set head off;
set echo off;

spool /home/oracle/tools/get_tablespace.tmp;

select TABLESPACE_NAME from dba_tablespaces;

exit;

get_tablespace_df.sql

-- set head off;
set linesize 99;
set pagesize 100;
set echo off;

spool /home/oracle/tools/show_tablespace_df.tmp;

set head off;
SELECT 'TABLESPACE Report' FROM dual;
-- SELECT TO_CHAR (sysdate, 'YYYY/MM/DD') FROM dual;

set head on;

SELECT sumdf.tablespace_name "Tablespace",
       TO_CHAR(sumdf.total_bytes, '999,999,999,990') "size(bytes)",
       TO_CHAR(sumdf.total_bytes - sumfs.free_bytes, '999,999,999,990') "Used",
       TO_CHAR(sumfs.free_bytes, '999,999,999,990') "Avail",
       TO_CHAR(100 - ((nvl(sumfs.free_bytes, 0) / sumdf.total_bytes) * 100), '990.99') || '%' "%"
FROM (SELECT df.tablespace_name, sum(df.bytes) total_bytes
      FROM dba_data_files df
      GROUP BY df.tablespace_name) sumdf
LEFT OUTER JOIN (SELECT fs.tablespace_name, sum(fs.bytes) free_bytes
                 FROM dba_free_space fs
                 GROUP BY fs.tablespace_name) sumfs
ON (sumdf.tablespace_name = sumfs.tablespace_name)
STR_TBLSPCS

exit;

get_tablespace_df.sh
最初のブロックで、全tablespace名を取得。次のブロックで、取得したtablespace名毎の利用率を取得。最後のブロックはおまけで、レポートをメールで通知。 nagios等利用できれば、利用率が80%超えたらアラート投げるとか。

#!/bin/sh

SQLPLUS=$HOME/database/product/10.1.0/db_1/bin/sqlplus
UP="sys/oracle  as sysdba"

#### --------------------------------------------------------------------------
#
#### --------------------------------------------------------------------------

$SQLPLUS $UP @get_tablespace_name.sql

grep -v "rows selected" get_tablespace.tmp > get_tablespace.log
rm -f get_tablespace.tmp


TBLSPCS=`cat get_tablespace.log`

STR_TBLSPCS=""

for TBLSPC in $TBLSPCS
do
    if [ "X" != "X$TBLSPC" ]; then
        if [ "X" != "X$STR_TBLSPCS" ]; then
            STR_TBLSPCS=`echo $STR_TBLSPCS,\'$TBLSPC\'`
        else
            STR_TBLSPCS=`echo \'$TBLSPC\'`
        fi
    fi

done
# echo "*** STR_TBLSPCS=[$STR_TBLSPCS]"

#### --------------------------------------------------------------------------
#
#### --------------------------------------------------------------------------
rm -f get_tablespace_df.sql.tmp
while read REC
do
    if [ "XSTR_TBLSPCS" = "X$REC" ]; then
        echo "WHERE sumdf.tablespace_name in ($STR_TBLSPCS);" >> get_tablespace_df.sql.tmp
    else
        echo "$REC" >> get_tablespace_df.sql.tmp
    fi

done < get_tablespace_df.sql

$SQLPLUS $UP @get_tablespace_df.sql.tmp
grep -v "rows selected" get_tablespace_df.tmp > get_tablespace_df.log
rm -f get_tablespace_df.sql.tmp get_tablespace_df.tmp

#### --------------------------------------------------------------------------
#
#### --------------------------------------------------------------------------
DATE=`date "+%Y/%m/%d"`
HOST=`hostname -s`
HEAD="Oracle tablespace report ($HOST - $DATE)"
BODY=`cat get_tablespace_df.log`
TO="[e-mail]"
FROM="[e-mail]"

SUBJECT="$HEAD
From: $FROM";
/bin/echo "$BODY" | /bin/mail -s "$SUBJECT" "$TO"

#### EOF