Orabbix Plugin monitors Oracle tablespace issues

After we install the Orabbix plug-in, look at the Tablespaces monitoring item is the hair item value is None, the first reaction is not monitoring success, not actually.

1, we open the path of the ORABBIX monitoring key parameter, the following is the SQL code of tablespaces

[[email protected] ~]# vim/opt/orabbix/conf/query.propsselect '-tablespace ', t.tablespace_name Ktablespace, '-type-> ', substr (t.contents, 1, 1) Tipo, '-Used (MB), ', Trunc (D.TBS_SIZE-NVL (S.free_spa CE, 0))/1024/1024) Ktbs_em_uso, '-actualsize (MB), trunc (d.tbs_size/1024/1024) ktbs_size, '-MaxSize (MB ), Trunc (d.tbs_maxsize/1024/1024) ktbs_maxsize, '-FreeSpace (MB) ', Trunc (NVL (s.free_space, 0)/1024/1024) k Free_space, '-space-> ', trunc ((D.tbs_maxsize-d.tbs_size + NVL (s.free_space, 0))/1024/1024) Kspace, '-P Erc-> ', decode (d.tbs_maxsize, 0, 0, trunc ((D.TBS_SIZE-NVL (s.free_space, 0)) *100/d.tbs_maxsize) Kperc from (select S UM (bytes) tbs_size, SUM (Decode (sign (maxbytes-bytes),-1, Bytes, maxbytes)) tbs_maxsize, Tablespace_name table Space from (select NVL (bytes, 0) bytes, NVL (maxbytes, 0) MaxBytes, tablespace_name from Dba_data_files Union All select Nvl (bytes, 0) bytes, NVL (MaxBytes, 0) MaxBytes, tablespace_name from Dba_temp_files) GROUP by Tablespace_name) d, (select SU M (bytes) free_space, Tablespace_name tablespace from Dba_free_space Group by Tablespace_name) s, Dba_     Tablespaces t where T.tablespace_name = D.tablespace (+) and T.tablespace_name = S.tablespace (+) Order by 8) where Kperc > and Tipo <> ' T ' and Tipo <> ' U ' tbl_space. Nodatafound=none

This SQL will return 93% full tablespace information, and corresponding to this monitoring item, Orabbix also defines the trigger, because the return value of the monitor item is text, and the return string "None" when the record is not satisfied, so the trigger of the monitor item checks whether the return value starts with none, if not , the alarm, so that users in addition to receive warning information, but also from the specific value of the return value to see when the table space is almost full.

Change to 3% after test result:

Of course, most of the time the monitoring item will return none, so we cannot draw a space-time curve for a normal, not full tablespace. Only when it is more than 93% slow, do we know the specific occupancy situation.

If you want to collect and retain more information, you need to use a custom query by adding the SQL that you want to check in the Query.props file, for example, if we want to know about tablespace information, we can put down sql:

Customtbl. Query=select ' TBL: ' | | a.tablespace_name| | ', ' TBL, ' total Size: ' | | Trunc (SUM (a.tots)/1024/1024, 2) | | ', ' TOT_SIZE_MB, ' free MB: ' | | Round (sum (a.sumb)/1024/1024, 2) | | ', ' tot_free_mb, ' PCT free: ' | | Round (SUM (a.sumb) * 100/sum (A.tots), 2) | | ', ' pct_free, ' Max free MB: ' | | Round (sum (a.largest)/1024/1024, 2) | | ', ' max_free_mb, ' Chunks free: ' | | SUM (a.chunks) | | ', ' chunks_free from (select Tablespace_name, 0 tots, sum (bytes) sumb, max (bytes) Largest, count (*) chunks From Dba_free_space a GROUP by Tablespace_name Union select tablespace_name, sum (bytes) Tots, 0, 0, 0 from Dba_data_files Group by Tablespace_name) a group by A.TABLESPACE_NAMECUSTOMTBL. Nodatafound=none

Orabbix Plugin monitors Oracle tablespace issues