zabbix监控oracle表空间使用率

[oracle@ip-10-10-40-2 oraclescripts]$ cat check_tablespace.sh 
#!/bin/bash
# tablespace usagep check
source ~/.bash_profile
function check {
sqlplus -S "/ as sysdba" <<  EOF
set linesize 200
set pagesize 200
spool /tmp/tablespace.log
select a.tablespace_name, total, free,(total-free) as usage from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
spool off
quit
EOF
};check &>/dev/null

 

[oracle@ip-10-10-40-2 oraclescripts]$ cat /tmp/tablespace.log 

TABLESPACE_NAME                     TOTAL       FREE      USAGE                                                                                                                                         
------------------------------ ---------- ---------- ----------                                                                                                                                         
ACTIVITY_TBS                           50         49          1                                                                                                                                         
SYSAUX                              15168   10794.25    4373.75                                                                                                                                         
UNDOTBS1                            98078  4421.1875 93656.8125                                                                                                                                         
UCEX_ACTIVITY_USER                     50    48.0625     1.9375                                                                                                                                         
UCEX_SERVICE_TBS                    10290 10172.6875   117.3125                                                                                                                                         
USERS                                   5     2.1875     2.8125                                                                                                                                         
SYSTEM                              11090  10236.625    853.375                                                                                                                                         
SLUCEX_TBS                             50         49          1                                                                                                                                         
SLUCEXTWO                      135135.563  38541.875 96593.6875                                                                                                                                         

9 rows selected.

View Code

 

[oracle@ip-10-10-40-2 oraclescripts]$ cat discovery_oracle_tablespace.sh 
#!/bin/bash
#zabbix discovery oracle tablespace
table_spaces=(`cat /tmp/tablespace.log | sed -e "1,3d" -e "/^$/d" -e "/selected/d" | awk '{print $1}'`)
length=${#table_spaces[@]}
 
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
    printf "\n\t\t{"
    printf "\"{#TABLESPACE_NAME}\":\"${table_spaces[$i]}\"}"
    if [ $i -lt $[$length-1] ];then
        printf ","
    fi
done
    printf "\n\t]\n"
printf "}\n"

 

[oracle@ip-10-10-40-2 oraclescripts]$ bash discovery_oracle_tablespace.sh 
{
    "data":[
        {"{#TABLESPACE_NAME}":"ACTIVITY_TBS"},
        {"{#TABLESPACE_NAME}":"SYSAUX"},
        {"{#TABLESPACE_NAME}":"UNDOTBS1"},
        {"{#TABLESPACE_NAME}":"UCEX_ACTIVITY_USER"},
        {"{#TABLESPACE_NAME}":"UCEX_SERVICE_TBS"},
        {"{#TABLESPACE_NAME}":"USERS"},
        {"{#TABLESPACE_NAME}":"SYSTEM"},
        {"{#TABLESPACE_NAME}":"SLUCEX_TBS"},
        {"{#TABLESPACE_NAME}":"SLUCEXTWO"}
    ]
}

View Code

 

[oracle@ip-10-10-40-2 oraclescripts]$ cat tablespace_check.sh 
#!/bin/bash
# oracle tablespace check
CEHCK_TYPE=$1
TABLESPACE_NAME=$2
 
function usagepre {
    grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | awk '{printf "%.f\n",($2-$3)/$2*100}'
}
 
function available {
    grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | awk '{printf $3*1024*1024}'
}
 
function check {
    if grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | awk '{print $2}' | uniq | grep "YES" &>/dev/null;then
        echo 1
    else
        echo 0
    fi
}
 
case $CEHCK_TYPE in
    pre)
        usagepre ;;
    fre)
        available ;;
    check)
        check ;;
    *)
        echo -e "Usage: $0 [pre|fre|check] [TABLESPACE_NAME]"
esac

 

[oracle@ip-10-10-40-2 oraclescripts]$ bash tablespace_check.sh pre SLUCEXTWO
71

View Code

 

oracle监控表空间使用率：

SQL> SELECT total.tablespace_name,
  2         Round(total.MB, 2)           AS Total_MB,
  3         Round(total.MB - free.MB, 2) AS Used_MB,
  4         Round(( 1 - free.MB / total.MB ) * 100, 2)
  5         || '%'                       AS Used_Pct
  6  FROM   (SELECT tablespace_name,
  7                 Sum(bytes) / 1024 / 1024 AS MB
  8          FROM   dba_free_space
  9          GROUP  BY tablespace_name) free,
 10         (SELECT tablespace_name,
 11                 Sum(bytes) / 1024 / 1024 AS MB
 12          FROM   dba_data_files
 13          GROUP  BY tablespace_name) total
 14  WHERE  free.tablespace_name = total.tablespace_name;

TABLESPACE_NAME                  TOTAL_MB    USED_MB USED_PCT                                 
------------------------------ ---------- ---------- -----------------------------------------
ACTIVITY_TBS         50          1 2%
SYSAUX      15168    4373.75 28.84%
UNDOTBS1      98078   93792.81 95.63%
UCEX_ACTIVITY_USER         50       1.94 3.88%
UCEX_SERVICE_TBS      10290     117.31 1.14%
USERS          5       2.81 56.25%
SYSTEM      11090     853.38 7.69%
SLUCEX_TBS         50          1 2%
SLUCEXTWO  135135.56   96593.69 71.48%

9 rows selected. 

 

转载于:https://www.cnblogs.com/penny-pan/p/10335385.html