oracle数据库表空间巡检脚本

#!/bin/bash
source /home/oracle/.bash_profile
sqlplus -S /nolog > checktablespace.log <<EOF
conn system/test
set linesize 500;
set heading off;
set pagesize 0;
set feedback off;
SELECT sysdate,a.tablespace_name “表空间名”,
total / (1024 * 1024 * 1024) “表空间大小(G)”,
free / (1024 * 1024 * 1024) “表空间剩余大小(G)”,
(total – free) / (1024 * 1024 * 1024) “表空间使用大小(G)”,
ROUND((total – free) / total, 4) * 100 “使用率%”
FROM (SELECT tablespace_name, SUM(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by “使用率%” desc;
quit
EOF

cat checktablespace.log|grep -v “SYSAUX” | awk ‘ $7 >95 {print $3,”使用率为:”,$7}’ > checktablespace1.log