通过crontab自动监测与管理表空间
一.设置crontab
32 21 * * * su - oracle -c "/script/chktbs.sh" >/dev/null 2>&1
root用户每天21:32执行32 21 * * * su - oracle -c "/script/chktbs.sh" >/dev/null 2>&1
二, chktbs.sh内容
sqlplus -s system/oracle >/oracle/tbsfree.alert << EOF
set feed off
set verify off
set heading off
set pagesize 0
set linesize 300
select f.tablespace_name ,f.free_space/d.total_space free_percen
from (select tablespace_name,sum(bytes) free_space
from dba_free_space group by tablespace_name) f,
(select tablespace_name,sum(bytes) total_space
from dba_data_files group by tablespace_name) d
where f.tablespace_name=d.tablespace_name
and f.free_space/d.total_space<0.40
/
exit
EOF
if [ `cat tbsfree.alert | wc -l` -gt 0 ]
then
cat tbsfree.alert | mail @qq.com -s "No enough free space in Orace db! "
fi
二.调整表空间方法
1.为表空间增加数据文件
alter tablespace ORGTBL_TBSP add datafile '/oradata/ansteip/ORGTBL_TBSP2.dbf' size 500M;
2.修改现有表空间数据文件大小
alter database datafile '/oradata/ansteip/BPMTBL_TBSP.dbf' resize 1000M;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24800471/viewspace-719172/ ，如需转载，请注明出处，否则将追究法律责任。
