检查表空间的使用代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】检查表空间的使用

作者:Dezai.CN / 发布于2013/5/29/ 1261


	#####################################################################
	## ck_tbsp.sh ##
	#####################################################################
	#!/bin/ksh

	EDITOR=vi; export EDITOR
	ORACLE_SID=PPRD10; export ORACLE_SID
	ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
	ORACLE_HOME=$ORACLE_BASE/10.2.0; export ORACLE_HOME
	LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
	TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN
	NLS_LANG=american; export NLS_LANG
	NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
	ORATAB=/var/opt/oracle/oratab;export ORATAB
	PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATH
	DBALIST="tianlesoftware@vip.qq.com,tianlesoftware@hotmail.com";export DBALIST


	sqlplus -s '/ as sysdba' <<EOF
	set feed off
	set linesize 100
	set pagesize 200
	column "USED (MB)" format a10
	column "FREE (MB)" format a10
	column "TOTAL (MB)" format a10
	column PER_FREE format a10
	spool tablespace.alert
	SELECT F.TABLESPACE_NAME,
	TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
	TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
	TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
	TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
	FROM (
	SELECT TABLESPACE_NAME,
	ROUND (SUM (BLOCKS*(SELECT VALUE/1024
	FROM V/$PARAMETER
	WHERE NAME = 'db_block_size')/1024)
	) FREE_SPACE
	FROM DBA_FREE_SPACE
	GROUP BY TABLESPACE_NAME
	) F,
	(
	SELECT TABLESPACE_NAME,
	ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
	FROM DBA_DATA_FILES
	GROUP BY TABLESPACE_NAME
	) T
	WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
	AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 80;
	spool off
	exit
	EOF
	if [ `cat tablespace.alert|wc -l` -gt 0 ]
	then
	cat tablespace.alert > tablespace.tmp
	mailx -s "TABLESPACE ALERT for PPRD10" $DBALIST < tablespace.tmp
	fi

	警告email输出的例子如下：
	TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE
	------------------------------ ---------- ---------- ---------- ----------
	SYSTEM 519 401 920 44 %
	MILLDATA 559 441 1,000 44 %
	SYSAUX 331 609 940 65 %
	MILLREPORTS 146 254 400 64 %

试试其它关键字

　表空间　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)