Hello,
This issue has been resolved latest Zabbix Agent2 and template - ZBXNEXT-6419

I'm already using the latest version and it's still not fixed. Please compare the values you get with dba_tablespace_usage_metrics.

Example of the tablespace SYSAUX on my test db:

• FILE_BYTES: 1562378240
• MAX_BYTES: 2097152000
• FREE_BYTES: 982056960
• USED_BYTES: 580321280
• USED_PCT_MAX: 74,5
• USED_FILE_PCT: 37,14

The correct value for USED_PCT_MAX is 27,67 and not 74,5.

(1562378240-982056960)/2097152000*100

(FILE_BYTES-USED_BYTES)/MAY_BYTES*100

74,5 is the percentage of the allocated space of a tablespace, not the usage.

In the Oracle template for Zabbix agent 2 USED_PCT_MAX is used as "Tablespace allocated, percent", so no change is needed for USED_PCT_MAX. I've changed USED_FILE_PCT to get the correct usage of the tablespace in percent and uploaded the patch to this ticket.

Hola, 

Aplique esta solución:

old: ROUND(DECODE(SUM(df.MAX_BYTES), 0, 0, (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)), 2) AS USED_PCT_MAX,

new: ROUND(DECODE(SUM(df.MAX_BYTES), 0, 0, (SUM(df.BYTES)-SUM(f.FREE)) / SUM(df.MAX_BYTES)* 100), 2) AS USED_PCT_MAX,

en:  template item Oracle: Get tablespaces stats        (db.odbc.get[tablespace_stats,"\{$ORACLE.DSN}"])

Y se solucionó para tablespaces de tipo PERMANENTE, no así para TEMPORARY Tablespaces...

Revisando la query , se debe además modificar la query mas abajo cambiando lo siguiente:

old: ROUND(DECODE(SUM(Y.MAX_BYTES), 0, 0, (SUM(Y.BYTES) / SUM (Y.MAX_BYTES) * 100)), 2) AS USED_PCT_MAX

new: ROUND(DECODE(SUM(Y.MAX_BYTES), 0, 0, (SUM(Y.BYTES) - SUM(Y.FREE)) / SUM(Y.MAX_BYTES)* 100), 2) AS USED_PCT_MAX,   

Con esto, el USED_PCT_MAX para tablespace TEMP será mostrado correctamente....

La query final con las dos correciones es la siguiente:

SELECT df.tablespace_name AS TABLESPACE, df.type AS TYPE, NVL(SUM(df.BYTES), 0) AS FILE_BYTES, NVL(SUM(df.MAX_BYTES), 0) AS MAX_BYTES, NVL(SUM(f.FREE), 0) AS FREE_BYTES, SUM(df.BYTES)-SUM(f.FREE) AS USED_BYTES, ROUND(DECODE(SUM(df.MAX_BYTES), 0, 0, (SUM(df.BYTES)-SUM(f.FREE)) / SUM(df.MAX_BYTES)* 100), 2) AS USED_PCT_MAX, ROUND(DECODE(SUM(df.BYTES), 0, 0,(SUM(df.BYTES)-SUM(f.FREE))/ SUM(df.BYTES)* 100), 2) AS USED_FILE_PCT, DECODE(df.STATUS, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS STATUS FROM ( SELECT ddf.file_id, dt.contents AS TYPE, dt.STATUS , ddf.file_name, ddf.tablespace_name, TRUNC(ddf.bytes) AS bytes, TRUNC(GREATEST(ddf.bytes, ddf.maxbytes)) AS max_bytes FROM dba_data_files ddf, dba_tablespaces dt WHERE ddf.tablespace_name = dt.tablespace_name ) df, ( SELECT TRUNC(SUM(bytes)) AS FREE, file_id FROM dba_free_space GROUP BY file_id ) f WHERE df.file_id = f.file_id GROUP BY df.tablespace_name, df.TYPE, df.status UNION ALL SELECT Y.name AS TABLESPACE, Y.type AS TYPE, NVL(SUM(Y.BYTES), 0) AS FILE_BYTES, NVL(SUM(Y.MAX_BYTES), 0) AS MAX_BYTES, NVL(MAX(NVL(Y.FREE_BYTES, 0)), 0) AS FREE, SUM(Y.BYTES)-SUM(Y.FREE_BYTES) AS USED_BYTES, ROUND(DECODE(SUM(Y.MAX_BYTES), 0, 0, (SUM(Y.BYTES) - SUM(Y.FREE)) / SUM(Y.MAX_BYTES)* 100), 2) AS USED_PCT_MAX, ROUND(DECODE(SUM(Y.BYTES), 0, 0,(SUM(Y.BYTES)-SUM(Y.FREE_BYTES))/ SUM(Y.BYTES)* 100), 2) AS USED_FILE_PCT, DECODE(Y.TBS_STATUS, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS STATUS FROM ( SELECT dtf.tablespace_name AS name, dt.contents AS TYPE, dt.STATUS AS tbs_status, dtf.status AS status, dtf.bytes AS bytes, (SELECT ((f.total_blocks - s.tot_used_blocks)* vp.value) FROM ( SELECT tablespace_name, SUM(used_blocks) tot_used_blocks FROM gv$sort_segment WHERE tablespace_name != 'DUMMY' GROUP BY tablespace_name) s, ( SELECT tablespace_name, SUM(blocks) total_blocks FROM dba_temp_files WHERE tablespace_name != 'DUMMY' GROUP BY tablespace_name) f, ( SELECT value FROM v$parameter WHERE name = 'db_block_size') vp WHERE f.tablespace_name = s.tablespace_name AND f.tablespace_name = dtf.tablespace_name ) AS free_bytes, CASE WHEN dtf.maxbytes = 0 THEN dtf.bytes ELSE dtf.maxbytes END AS max_bytes FROM sys.dba_temp_files dtf, sys.dba_tablespaces dt WHERE dtf.tablespace_name = dt.tablespace_name ) Y GROUP BY Y.name, Y.TYPE, Y.tbs_status;

Hello,

with this small (corrected) patch I get the same values for USED_FILE_PCT as in dba_tablespace_usage_metrics (USED_PERCENT).

Any progress here? We faced the same issue.

Another workaround is to patch the template

```

min(/kvasy Oracle by Zabbix agent 2/oracle.tbs_used_bytes"{#TABLESPACE}",5m)/min(/kvasy Oracle by Zabbix agent 2/oracle.tbs_max_bytes"{#TABLESPACE}",5m)>{$ORACLE.TBS.UTIL.PCT.MAX.HIGH}

```{}

All metrics here are working as they are expected to. When creating tablespaces in Oracle, you can define 2 types of sizes - SIZE and MAXSIZE to which it can autoextend.

Explanation

USED_FILE_PCT

This metric calculates percentage of currently used bytes from currently allocated tablespace size (in Oracle terms - SIZE), i.e., how much is currently used from SIZE.

(USED_BYTES / FILE_BYTES) * 100

USED_PCT_MAX

This metric calculates percentage of currently allocated tablespace size to its maximum allowed size, i.e., how much SIZE is of MAXSIZE. This metric is useful to see when your tablespace allocated size is nearing its maximum allowed size.

(FILE_BYTES / MAX_BYTES) * 100

Item logic is also mentioned in respective item descriptions.

Solution

I understand that there is a need for a metric that shows percentage of tablespace current byte usage from its maximum allowed size (USED_BYTES from MAXSIZE)

(USED_BYTES / MAX_BYTES) * 100

We will work on implementing this for Oracle Agent2 and ODBC templates

Implemented in:

• 6.0.24rc1 cb72ef6ac25
• 6.4.9rc1 2d291499ae7
• 7.0.0alpha8 (master) 5adedd849fa