oracle脚本

最新推荐文章于 2022-09-20 00:25:04 发布

转载最新推荐文章于 2022-09-20 00:25:04 发布 · 495 阅读

16 篇文章

订阅专栏

本文分享了一个用于检查Oracle数据库表空间使用情况的bash脚本。该脚本通过sqlplus连接到数据库，查询每个表空间的已用空间、剩余空间及总空间，并将低于80%自由空间的表空间信息发送邮件通知给管理员。

#!/bin/bash
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1; export ORACLE_HOME
$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.;
export PATH
DBALIST="masicong1019@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