oracle自动扩展表空间测试,Oracle监视表空间,并自动增加数据文件脚本

转自：http://lovejuan1314.javaeye.com/blog/413546

--- 创建view --- 百分比 create view tablespace_used_percent as select useage from ( select a.tablespace_name, a.file_name, a.total "Total(MB)", round(a.total-b.Free_Space) "Used(MB)", round(((a.total-b.Free_Space)/a.total)*100,2) as useage, a.auto_extend from (select FILE_ID, tablespace_name, file_name, bytes/(1024*1024) Total, AUTOEXTENSIBLE auto_extend from dba_data_files ddf) a, (select file_id, sum(bytes)/(1024*1024) Free_Space from dba_free_space group by file_id) b where a.file_id=b.file_id ) where tablespace_name = 'YOUR TABLESPACE NAME'; --- deails create view TABLESPACE_USAGE as select a.tablespace_name, a.file_name, a.total "Total(MB)", round(a.total-b.Free_Space) "Used(MB)", round(((a.total-b.Free_Space)/a.total)*100,2) "Used(%)", a.auto_extend from (select FILE_ID, tablespace_name, file_name, bytes/(1024*1024) Total, AUTOEXTENSIBLE auto_extend from dba_data_files ddf) a, (select file_id, sum(bytes)/(1024*1024) Free_Space from dba_free_space group by file_id) b where a.file_id=b.file_id; Linux 脚本 ##################################################################### ## checkTabsp.sh ## ## This Script will add the new datafile if  Tablespace's data ## file, which is greater than  the 80% of one datafiles size ##################################################################### #!/bin/bash usedPercentNO=(`sqlplus -s '/as sysdba' <= 80 then we add new data file,which will have 8G size       if [ $arrNo -gt 80 ]       then         let sigNo=$i+1      sqlplus -s "/ as sysdba" <tablespace.tmp   mailx -s "TABLESPACE ALERT for DB"  EMAIL-ADDRESS  < tablespace.alert   fi 上面这个脚本会导致如果有一个文件超过80%的话,脚本会不停添加数据文件.... 更新修改后的,而且把sql直接用文本文件来代替了以前使用的view #!/bin/bash # Managed by Puppet ##################################################################### ## checkTabsp.sh ## ## This Script will add the new datafile if BOCC Tablespace's data ## file, which is greater than  the 80% of one datafiles size ##################################################################### # Avoid have the script run if already running source /opt/app/inc/some_functions.sh pgrpfile=/tmp/checkTabsp.pgrp check_if_running # end source /home/oracle/.profile usedDatafileNO=(`sqlplus -s '/as sysdba' <tablespace.tmp #  mailx -s "TABLESPACE ALERT for DB" YOUR_EMAIL_ADDRESS t  < tablespace.alert #  fi