Oracle监视表空间,并自动增加数据文件脚本
2018-09-16
Oracle监视表空间,并自动增加数据文件脚本 Sql代码 --- 创建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 脚本 Java代码 ##################################################################### ## 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 Java代码 #!/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
<
免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。
http://www.pinlue.com/style/images/nopic.gif