当前位置:数据库 > Oracle >>

Oracle监视表空间,并自动增加数据文件脚本

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' <<\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 
 
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' <<\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  
<
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,