Oracle监视表空间并自动增加数据文件脚本.docx
《Oracle监视表空间并自动增加数据文件脚本.docx》由会员分享,可在线阅读,更多相关《Oracle监视表空间并自动增加数据文件脚本.docx(7页珍藏版)》请在冰豆网上搜索。
Oracle监视表空间并自动增加数据文件脚本
Oracle监视表空间,并自动增加数据文件脚本
Oracle监视表空间,并自动增加数据文件脚本
Sql代码
---创建view
---百分比
createviewtablespace_used_percentas
selectuseagefrom
(
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)asuseage,
a.auto_extend
from
(select
FILE_ID,
tablespace_name,
file_name,
bytes/(1024*1024)Total,
AUTOEXTENSIBLEauto_extend
from
dba_data_filesddf)a,
(select
file_id,
sum(bytes)/(1024*1024)Free_Space
from
dba_free_space
groupbyfile_id)b
where
a.file_id=b.file_id
)
wheretablespace_name='YOURTABLESPACENAME';
---deails
createviewTABLESPACE_USAGEas
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,
AUTOEXTENSIBLEauto_extend
from
dba_data_filesddf)a,
(select
file_id,
sum(bytes)/(1024*1024)Free_Space
from
dba_free_space
groupbyfile_id)b
where
a.file_id=b.file_id;
Linux脚本
Java代码
#####################################################################
##checkTabsp.sh##
##ThisScriptwilladdthenewdatafileifTablespace'sdata
##file,whichisgreaterthanthe80%ofonedatafilessize
#####################################################################
#!
/bin/bash
usedPercentNO=(`sqlplus-s'/assysdba'<<\EOF
SETheadingOFF;
SETverifyOFF;
SELECT*FROMtablespace_used_percent;
EOF`
)
#getthelengthofarray
len=${#usedPercentNO[*]}
echo"Thearrayhas$lenmembers."
i=0
while[$i-lt$len];do
echo"$i:
${usedPercentNO[$i]}"
arrNo=`echo"${usedPercentNO[$i]}"|awk-F.'{print$1}'`
if[-z$arrNo]
then
arrNo=1
fi
#ifusedPercentNo>=80thenweaddnewdatafile,whichwillhave8Gsize
if[$arrNo-gt80]
then
letsigNo=$i+1
sqlplus-s"/assysdba"<<EOF
ALTERTABLESPACEDB_TABLESPACEADDDATAFILE'/opt/oracle/oradata/DB/DB_DATA$sigNo.dbf'SIZE2GAUTOEXTENDONMAXSIZE8G;
EOF
#weneedsendemailtoreportthetablespacestatsinfo
sqlplus-s"/assysdba"<<\EOF
coltablespace_namefora30
colfile_namefora60
colauto_extendfora12
coltablespace_namejustifycenter
colfile_namejustifycenter
colautoextendjustifyright
setlinesize200
setpagesize500
SPOOLtablespace.alert
SELECT*FROMTABLESPACE_USAGE;
SPOOLOFF;
EXIT
EOF
fi
leti++
done
#weneedn'tsendemailfromtherethecrontabwilldo
if[`cattablespace.alert|wc-l`-gt0]
then
cattablespace.alert>tablespace.tmp
mailx-s"TABLESPACEALERTforDB"EMAIL-ADDRESS<tablespace.alert
fi
上面这个脚本会导致如果有一个文件超过80%的话,脚本会不停添加数据文件....
更新修改后的,而且把sql直接用文本文件来代替了以前使用的view
Java代码
#!
/bin/bash
#ManagedbyPuppet
#####################################################################
##checkTabsp.sh##
##ThisScriptwilladdthenewdatafileifBOCCTablespace'sdata
##file,whichisgreaterthanthe80%ofonedatafilessize
#####################################################################
#Avoidhavethescriptrunifalreadyrunning
source/opt/app/inc/some_functions.sh
pgrpfile=/tmp/checkTabsp.pgrp
check_if_running
#end
source/home/oracle/.profile
usedDatafileNO=(`sqlplus-s'/assysdba'<<\EOF
SETheadingOFF;
SETverifyOFF;
@/opt/app/sql/chktabspused.sql
EOF`
)
#checkwhetheritneedsadddatafile
if[$usedDatafileNO-eq0]
then
usedDatNO=(`sqlplus-s'/assysdba'<<\EOF
SETheadingOFF;
SETverifyOFF;
@/opt/app/sql/chkdatno.sql
EOF`
)
letsigNO=$usedDatNO+1
sigNO=`printf"%03d"$sigNO`
sqlplus-s"/assysdba"<<EOF
ALTERTABLESPACEDB_TABLESPACEADDDATAFILE'/opt/oracle/oradata/DB/DB_DATA$sigNO.dbf'SIZE500MAUTOEXTENDONNEXT50M;
EOF
#weneedsendemailtoreportthetablespacestatsinfotocheckwhetheradddatafilesuccessful
sqlplus-s"/assysdba"<<\EOF
coltablespace_namefora30
colfile_namefora60
colauto_extendfora12
coltablespace_namejustifycenter
colfile_namejustifycenter
colautoextendjustifyright
setlinesize200
setpagesize500
@/opt/bocc/sql/chktabspstats.sql
EXIT
EOF
#outputthediskspaceuseage
df-h
fi
#wewilldon'tsendemailfromtherethecrontabwilldo
#if[`cattablespace.alert|wc-l`-gt0]
#then
#cattablespace.alert>tablespace.tmp
#mailx-s"TABLESPACEALERTforDB"YOUR_EMAIL_ADDRESSt<tablespace.alert