自动监控oracle 表空间使用量的脚本

from：http://yong321.freeshell.org/oranotes/TablespaceFreespace.txt 

Believe it or not, some DBAs don't write tablespace freespace check scripts
correctly. A common mistake occurs when the free space is so small even one
extent can't be created in the tablespace and the tablespace name disappears
from dba_free_space. Many scripts such as the one in Metalink Note:1019999.6,
not using outer joins between dba_data_files and dba_free_space, or using
outer joins but with a simple freespace > somepercentage without NVL, miss
those tablespaces that need space the most.

不管你信不信，有些dba都不会写监控表空间使用情况的脚本，当空闲空间较小时，（甚至不能在表空间中创建一个extent，或者在dba_free_space找不到某个表空间的名字）就会出现一些常规的问题其他脚本可以参考mos：1019999.6

--List tablespaces whose usage exceeds 90%

--列出使用量超过90%的表空间
select a.tablespace_name, totalspace, nvl(freespace,0) freespace,
   (totalspace-nvl(freespace,0)) used,
  ((totalspace-nvl(freespace,0))/totalspace)*100 "%USED"
from
  (select tablespace_name, sum(bytes)/1048576 totalspace
   from dba_data_files
   group by tablespace_name) a,
  (select tablespace_name, sum(Bytes)/1048576 freespace
   from dba_free_space
   group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
  and ((totalspace-nvl(freespace,0))/totalspace)*100 > 90
  --and nvl(freespace,0) < 1000  -- only list TSs < 1GB free
order by 5 desc
/

 

--in case some datafiles are autoextensible, and exclude read only TS's

--特例：某些数据文件是可自动扩展的，不包含只读的表空间
select a.tablespace_name, cur_total,
  nvl(freespace,0) cur_free, (cur_total-nvl(freespace,0)) cur_used,
  (nvl(freespace,0)/cur_total)*100 "%CUR_FREE",  --%freespace not considering autoextension
  max_can_ext2, --max this TS can extend to
  (nvl(freespace,0)+max_can_ext2-cur_total)/max_can_ext2*100 "%MAX_FREE" --%freespace considering autoextension
from
  (select tablespace_name, sum(bytes)/1048576 cur_total,
     sum(decode(maxbytes,0,bytes,greatest(maxbytes,bytes)))/1048576 max_can_ext2 --even if autoextensible, maxbytes may be < bytes
   from dba_data_files
   group by tablespace_name) a,
  (select tablespace_name, sum(bytes)/1048576 freespace
   from dba_free_space
   group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
  and (nvl(freespace,0)+max_can_ext2-cur_total)/max_can_ext2*100 < 10  --freespace < 10%, considering autoextension
  and a.tablespace_name not in
      (select tablespace_name from dba_tablespaces where status = 'READ ONLY')
  and a.tablespace_name != 'AUDIT_DATA' -- this TS is checked manually
order by 7;

--If it runs slow, make sure recyclebin$ or dba_recyclebin is empty or near empty.

--如果以上脚本运行的很慢，请确定recyclebin$ 或者dba_recyclebin 是否为空，或者接近为空。

 

Complete shell script ready for a cron job:

完整的shell脚本如下：

 

 

#!/bin/bash
#ck_freespace.sh: Check tablespace free space.

export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin

RECIPIENT=yong321,fellowdba
PCTFREE_THRHLD=15       #percent free out of max size that can be extended to

cd /u01/app/oracle/scripts/ck_ts_fs
sqlplus -s -L '/ as sysdba' <<EOF
set pages 1000 feedb off
spo ck_ts_fs.lst
select a.tablespace_name, cur_total,
  nvl(freespace,0) cur_free, (cur_total-nvl(freespace,0)) cur_used,
  (nvl(freespace,0)/cur_total)*100 "%CUR_FREE",  --%freespace not considering autoextension
  max_can_ext2, --max this TS can extend to
  (nvl(freespace,0)+max_can_ext2-cur_total)/max_can_ext2*100 "%MAX_FREE" --%freespace considering autoextension
from
  (select tablespace_name, sum(bytes)/1048576 cur_total,
     sum(decode(maxbytes,0,bytes,greatest(maxbytes,bytes)))/1048576 max_can_ext2 --even if autoextensible, maxbytes may be < bytes
   from dba_data_files
   group by tablespace_name) a,
  (select tablespace_name, sum(bytes)/1048576 freespace
   from dba_free_space
   group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
  and (nvl(freespace,0)+max_can_ext2-cur_total)/max_can_ext2*100 < $PCTFREE_THRHLD  --%freespace, considering autoextension
  and a.tablespace_name not in
      (select tablespace_name from dba_tablespaces where status = 'READ ONLY')
  and a.tablespace_name != 'AUDIT_DATA' -- this TS is checked manually
order by 7;
exit
EOF

if [[ $(wc -l ck_ts_fs.lst|awk '{print $1}') -le 3 ]]; then
  echo "All tablespaces are above $PCTFREE_THRHLD percent free!"
else
  echo "At least one tablespace has lower than $PCTFREE_THRHLD percent free space (even considering autoextension if configured)!"
  mail -s "Warning: Tablespace free space alert on $ORACLE_SID" $RECIPIENT < ck_ts_fs.lst
fi