- --- 创建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' <<\EOF
- SET heading OFF;
- SET verify OFF;
- SELECT * FROM tablespace_used_percent;
- EOF`
- )
- #get the length of array
- len=${#usedPercentNO[*]}
- echo "The array has $len members."
- 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
- # if usedPercentNo >= 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" <<EOF
- ALTER TABLESPACE DB_TABLESPACE ADD DATAFILE '/opt/oracle/oradata/DB/DB_DATA$sigNo.dbf' SIZE 2G AUTOEXTEND ON MAXSIZE 8G;
- EOF
- # we need send email to report the tablespace stats info
- sqlplus -s "/as sysdba" <<\EOF
- col tablespace_name for a30
- col file_name for a60
- col auto_extend for a12
- col tablespace_name justify center
- col file_name justify center
- col autoextend justify right
- set linesize 200
- set pagesize 500
- SPOOL tablespace.alert
- SELECT * FROM TABLESPACE_USAGE;
- SPOOL OFF;
- EXIT
- EOF
- fi
- let i++
- done
- #we needn't send email from there the crontab will do
- if [ `cat tablespace.alert|wc -l` -gt 0 ]
- then
- cat tablespace.alert >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' <<\EOF
- SET heading OFF;
- SET verify OFF;
- @/opt/app/sql/chktabspused.sql
- EOF`
- )
- # check whether it needs add data file
- if [ $usedDatafileNO -eq 0 ]
- then
- usedDatNO=(`sqlplus -s '/as sysdba' <<\EOF
- SET heading OFF;
- SET verify OFF;
- @/opt/app/sql/chkdatno.sql
- EOF`
- )
- let sigNO=$usedDatNO+1
- sigNO=`printf "%03d" $sigNO`
- sqlplus -s "/ as sysdba" <<EOF
- ALTER TABLESPACE DB_TABLESPACE ADD DATAFILE '/opt/oracle/oradata/DB/DB_DATA$sigNO.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M;
- EOF
- # we need send email to report the tablespace stats info to check whether add data file successful
- sqlplus -s "/as sysdba" <<\EOF
- col tablespace_name for a30
- col file_name for a60
- col auto_extend for a12
- col tablespace_name justify center
- col file_name justify center
- col autoextend justify right
- set linesize 200
- set pagesize 500
- @/opt/bocc/sql/chktabspstats.sql
- EXIT
- EOF
- # out put the disk space useage
- df -h
- fi
- #we will don't send email from there the crontab will do
- # if [ `cat tablespace.alert|wc -l` -gt 0 ]
- # then
- # cat tablespace.alert >tablespace.tmp
- # mailx -s "TABLESPACE ALERT for DB" YOUR_EMAIL_ADDRESS t < tablespace.alert
- # fi