zabbix监控Oracle表空间使用率

一、获取Oracle数据库表空间信息的原始脚本和自动发现脚本

 Oracle表空间的信息需要SQL语句查询得到，因此，我们首先创建一个获取表空间信息的原始脚本/home/oracle/tablespace.sh，这个脚本由oracle用户去执行，脚本编写如下： 

确认环境变量：

[root@racdb1 tmp]# cat /home/oracle/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
################OracleBegin#########################
umask 022
export TMP=/tmp
export TMPDIR=$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db
export ORACLE_HOSTNAME=p19c01
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=plm1
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
alias sas='sqlplus / as sysdba'
export PS1="[`whoami`@`hostname`:"'$PWD]$ '
################OracleEnd#########################

[root@racdb1 ~]# cat /home/oracle/tablespace.sh
#!/bin/bash

#export ORACLE_BASE=/u01/app/oracle
#export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db
#export PATH=$ORACLE_HOME/bin:$PATH
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
source ~/.bash_profile  #执行此步可确保定时任务能执行成功，否则任务可能找不到变量

sqlplus  -S / as sysdba <<EOF
set heading off
set pagesize 0
set feedback off
set linesize 200

spool /tmp/ora_tablespace.txt

SELECT aa.tablespace_name,
       round(aa.usd / bb.maxs * 100, 2) "USED%",
       round((bb.maxs - aa.usd) / 1024 / 1024 / 1024, 2) "FREE(G)",
       round(bb.maxs / 1024 / 1024 / 1024, 2) "MAX(G)",
       round(aa.usd / 1024 / 1024 / 1024, 2) "USED(G)"
  FROM (SELECT b.Tablespace_Name, (b.Bytes - NVL(a.Bytes, 0)) "USD"
          FROM (SELECT tablespace_name, SUM(bytes) bytes
                  FROM Dba_Free_Space
                 GROUP BY tablespace_name) a,
               (SELECT tablespace_name, SUM(bytes) bytes
                  FROM Dba_Data_Files
                 GROUP BY tablespace_name) b
         WHERE b.tablespace_name = a.tablespace_name(+)
        ) aa,
       (SELECT tablespace_name,
               SUM(CASE
                     WHEN maxbytes = 0 THEN
                      user_bytes
                     ELSE
                      maxbytes