Home / Database / Script to check tablespace usage.

Script to check tablespace usage.

This script checks for tablespace usage. If tablespace is 10 percent free,  it will send an alert e-mail.

#####################################################################
## ck_tbsp.sh ##
#####################################################################
#!/bin/ksh
sqlplus -s <<!
oracle/$1@$2
set feed off
set linesize 100
set pagesize 200
spool tablespace.alert
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USED (MB)”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE (MB)”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTAL (MB)”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′)||’ %’ PER_FREE
FROM   (
SELECT       TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = ‘db_block_size’)/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
spool off
exit
!
if [ `cat tablespace.alert|wc -l` -gt 0 ] then
cat tablespace.alert -l tablespace.alert > tablespace.tmp
mailx -s “TABLESPACE ALERT for ${2}” $DBALIST < tablespace.tmp
fi

 

An example of the alert mail output is as follows:

TABLESPACE_NAME     USED (MB)   FREE (MB)         TOTAL (MB)            PER_FREE
——————- ——— ———– ——————- ——————
SYSTEM              2,047             203               2,250                9 %
STBS01                302              25                327                 8 %
STBS02                241              11                252                 4 %
STBS03                233              19                252                 8 %

2 comments

  1. Do you have it as windows batch?

Leave a Reply