[#ZBX-18514] Item "Get tablespaces stats"

Comment by Alexey Sedoykin [ 2020 Oct 17 ]

I tried to execute this SQL query via sqlplus (on zabbix side) and this query works well, but via isql, I got:
[37000][Oracle][ODBC][Ora]ORA-00900

Comment by Alexey Pustovalov [ 2020 Oct 19 ]

Is it possible to share the query you tried to execute? Do you use vanilla Zabbix 5.0 with built-in Oracle template?

Comment by Alexey Sedoykin [ 2020 Oct 19 ]

Dear Alexey, yes, I am using vanilla Zabbix 5.0 and I took Oracle template from Zabbix web-site ( https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/oracle_odbc)

SELECT
            df.tablespace_name AS tablespace,
            df.type AS TYPE,
            SUM(df.bytes) AS used_bytes,
            SUM(df.max_bytes) AS max_bytes,
            SUM(f.free) AS free_bytes,
            ROUND(SUM(df.bytes)/ SUM(df.max_bytes)* 100, 2) AS used_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_name,
               Y.type AS TYPE,
               SUM(Y.bytes) AS bytes,
               SUM(Y.max_bytes) AS max_bytes,
               MAX(NVL(Y.free_bytes, 0)) AS FREE,
               ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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 ORDER BY tablespace;

Comment by Alexey Pustovalov [ 2020 Oct 19 ]

Thank you! Could you confirm that you have Oracle 12 release 1 (12.1.0.2.0)?

Comment by Alexey Sedoykin [ 2020 Oct 19 ]

Yep,

Dear Alexey, Pay attention, what this query can be execute via Oracle SQL Developer, and other queries from this template - works well

Add to Phrasebook
- Hard words for me
- Create a new word list...
Copy

Comment by Alexey Pustovalov [ 2020 Oct 19 ]

Did you execute the query without lines split? Did you try isql or sqlplus? Any chance to check the query through GUI based SQL browser? it can show where exactly there is a error.

Comment by Alexey Sedoykin [ 2020 Oct 19 ]

This query can be executing via sqlplus (from zabbix side, or monitored side) - without problem, I tried to delete split lines, as result same result.

>>Any chance to check the query through GUI based SQL browser?
Just kick me in the right way, i will be try it

Comment by Alexey Pustovalov [ 2020 Oct 19 ]

It is possible to attach screenshot where you execute query? Does it show exact place where SQL is not correct?

Comment by Alexey Sedoykin [ 2020 Oct 19 ]

Dear Alex, in zabbix_server.log I saw the next message:

2379:20201016:180821.400 error reason for "SRVTC_ORADB:db.odbc.get[tablespace_stats,"\{$ORACLE.DSN}"]" changed: Cannot execute ODBC query: [SQL_ERROR]:[HY000][907][[Oracle][ODBC][Ora]ORA-00907: отсутствует правая скобка
]
2376:20201016:181201.004 error reason for "SRVTC_ORADB:db.odbc.get[tablespace_stats,"\{$ORACLE.DSN}"]" changed: Cannot execute ODBC query: [SQL_ERROR]:[HY000][933][[Oracle][ODBC][Ora]ORA-00933: неверное завершение SQL-предложения
]

Add to Phrasebook
- Hard words for me
- Create a new word list...
Copy

Comment by Alexey Pustovalov [ 2020 Oct 19 ]

Did you modify the query between 180821.400 and 181201.004 ?

Comment by Alexey Sedoykin [ 2020 Oct 19 ]

Dear Alexey, mmm, it was my research, now in logs I saw just:

2023:20201019:223148.369 itemid:35900 hostid:10348 key:'db.odbc.get[tablespace_stats,"{$ORACLE.DSN}"]'
  2023:20201019:223148.369   type:11 value_type:4
  2023:20201019:223148.369   interfaceid:0
  2023:20201019:223148.369   state:1 error:'Cannot execute ODBC query: [SQL_ERROR]:[HY000][933][[Oracle][ODBC][Ora]ORA-00933: неверное завершение SQL-предложения
]'
  2023:20201019:223148.369   flags:0 status:0
  2023:20201019:223148.369   valuemapid:0
  2023:20201019:223148.369   lastlogsize:0 mtime:0
  2023:20201019:223148.369   delay:'1m' nextcheck:1603136300 lastclock:1603135700
  2023:20201019:223148.369   data_expected_from:1603135061
  2023:20201019:223148.369   history:0 history_sec:0
  2023:20201019:223148.369   poller_type:0 location:1
  2023:20201019:223148.369   inventory_link:0
  2023:20201019:223148.369   priority:1 schedulable:1
  2023:20201019:223148.369   db:[params:'SELECT df.tablespace_name AS tablespace,
            df.type AS TYPE,
            SUM(df.bytes) AS used_bytes,
            SUM(df.max_bytes) AS max_bytes,
            SUM(f.free) AS free_bytes,
            ROUND(SUM(df.bytes)/ SUM(df.max_bytes)* 100, 2) AS used_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_name,
               Y.type AS TYPE,
               SUM(Y.bytes) AS bytes,
               SUM(Y.max_bytes) AS max_bytes,
               MAX(NVL(Y.free_bytes, 0)) AS FREE,
               ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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 ORDER BY tablespace;' username:'{$ORACLE.USER}' password:'{$ORACLE.PASSWORD}']
  2023:20201019:223148.369   dependent:
  2023:20201019:223148.369     itemid:36016 flags:4
  2023:20201019:223148.369     itemid:36017 flags:4
  2023:20201019:223148.369     itemid:36018 flags:4
  2023:20201019:223148.369     itemid:36019 flags:4
  2023:20201019:223148.369     itemid:36020 flags:4
  2023:20201019:223148.369     itemid:36021 flags:4
  2023:20201019:223148.369     itemid:36022 flags:4
  2023:20201019:223148.369     itemid:36023 flags:4
  2023:20201019:223148.369     itemid:36024 flags:4
  2023:20201019:223148.369     itemid:36025 flags:4
  2023:20201019:223148.369     itemid:36026 flags:4
  2023:20201019:223148.369     itemid:36027 flags:4

And nothing more I tried to use mode 5 for logs file in zabbix

Comment by Alexey Pustovalov [ 2020 Oct 19 ]

What unixodbc and Oracle library version for you use?

Comment by Alexey Pustovalov [ 2020 Oct 19 ]

Please try also the following SQL:

SELECT df.tablespace_name AS tablespace, df.type AS TYPE, SUM(df.bytes) AS used_bytes, SUM(df.max_bytes) AS max_bytes, SUM(f.free) AS free_bytes, ROUND(SUM(df.bytes)/ SUM(df.max_bytes)* 100, 2) AS used_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_name, Y.type AS TYPE, SUM(Y.bytes) AS bytes, SUM(Y.max_bytes) AS max_bytes, MAX(NVL(Y.free_bytes, 0)) AS FREE, ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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 ORDER BY tablespace;

Comment by Alexey Sedoykin [ 2020 Oct 20 ]

Dear Alexey, I tried to execute the last variant of query - it does not work

Oracle libraries:
12.1.0.2.0-1.x86_64

unixODBC:
isql --version : unixODBC 2.3.7

Comment by Alexey Pustovalov [ 2020 Oct 20 ]

Please try to update Oracle libraries to newer and supported version + try to execute the query by subqueries. For example:

SELECT
   df.tablespace_name AS tablespace,
   df.type AS TYPE,
   SUM(df.bytes) AS used_bytes,
   SUM(df.max_bytes) AS max_bytes,
   SUM(f.free) AS free_bytes,
   ROUND(SUM(df.bytes) / SUM(df.max_bytes)* 100, 2) AS used_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

then

SELECT
   Y.name AS tablespace_name,
   Y.type AS TYPE,
   SUM(Y.bytes) AS bytes,
   SUM(Y.max_bytes) AS max_bytes,
   MAX(NVL(Y.free_bytes, 0)) AS FREE,
   ROUND(SUM(Y.bytes) / SUM(Y.max_bytes)* 100, 2) AS used_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

Comment by Alexey Sedoykin [ 2020 Oct 20 ]

Dear Alexey, based your idea, I cut the original query on two separate parts and represent them like a single string:

SELECT df.tablespace_name AS tablespace, df.type AS TYPE, SUM(df.bytes) AS used_bytes, SUM(df.max_bytes) AS max_bytes, SUM(f.free) AS free_bytes, ROUND(SUM(df.bytes) / SUM(df.max_bytes)* 100, 2) AS used_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

SELECT Y.name AS tablespace_name, Y.type AS TYPE, SUM(Y.bytes) AS bytes, SUM(Y.max_bytes) AS max_bytes, MAX(NVL(Y.free_bytes, 0)) AS FREE, ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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

But, unfortunately I still cannot to execute this queries as a single query

Comment by Alexey Pustovalov [ 2020 Oct 20 ]

What about this queries:

SELECT df.tablespace_name AS tablespace,
            df.type AS TYPE,
            SUM(df.bytes) AS used_bytes,
            SUM(df.max_bytes) AS max_bytes,
            SUM(f.free) AS free_bytes,
            ROUND(SUM(df.bytes)/ SUM(df.max_bytes)* 100, 2) AS used_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_name,
               Y.type AS TYPE,
               SUM(Y.bytes) AS bytes,
               SUM(Y.max_bytes) AS max_bytes,
               MAX(NVL(Y.free_bytes, 0)) AS FREE,
               ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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

SELECT
               Y.name AS tablespace_name,
               Y.type AS TYPE,
               SUM(Y.bytes) AS bytes,
               SUM(Y.max_bytes) AS max_bytes,
               MAX(NVL(Y.free_bytes, 0)) AS FREE,
               ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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 ORDER BY tablespace

Comment by Alexey Sedoykin [ 2020 Oct 20 ]

Dear Alexey,

the first variant after converting in single line:

SELECT df.tablespace_name AS tablespace, df.type AS TYPE, SUM(df.bytes) AS used_bytes, SUM(df.max_bytes) AS max_bytes, SUM(f.free) AS free_bytes, ROUND(SUM(df.bytes)/ SUM(df.max_bytes)* 100, 2) AS used_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_name, Y.type AS TYPE, SUM(Y.bytes) AS bytes, SUM(Y.max_bytes) AS max_bytes, MAX(NVL(Y.free_bytes, 0)) AS FREE, ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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

It works well! and I am getting information about tablespace in Zabbix
Thank you very much for help, this ticket can be closed, and I hope that the official template will be fixed.

Add to Phrasebook
- Hard words for me
- Create a new word list...
Copy

Comment by Alexey Pustovalov [ 2020 Oct 20 ]

and second please

Comment by Alexey Sedoykin [ 2020 Oct 20 ]

Dear Alexey, second query, after converting in single line - does not work, problem was in last sentence ORDER BY tablespace, i change it on ORDER BY tablespace_name

SELECT Y.name AS tablespace_name, Y.type AS TYPE, SUM(Y.bytes) AS bytes, SUM(Y.max_bytes) AS max_bytes, MAX(NVL(Y.free_bytes, 0)) AS FREE, ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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 ORDER BY tablespace_name

Comment by Alexey Pustovalov [ 2020 Oct 20 ]

Nice catch. Now last check please:

SELECT df.tablespace_name AS tablespace,
            df.type AS TYPE,
            SUM(df.bytes) AS used_bytes,
            SUM(df.max_bytes) AS max_bytes,
            SUM(f.free) AS free_bytes,
            ROUND(SUM(df.bytes)/ SUM(df.max_bytes)* 100, 2) AS used_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_name,
               Y.type AS TYPE,
               SUM(Y.bytes) AS bytes,
               SUM(Y.max_bytes) AS max_bytes,
               MAX(NVL(Y.free_bytes, 0)) AS FREE,
               ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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;

Comment by Alexey Sedoykin [ 2020 Oct 20 ]

Nope

SELECT df.tablespace_name AS tablespace, df.type AS TYPE, SUM(df.bytes) AS used_bytes, SUM(df.max_bytes) AS max_bytes, SUM(f.free) AS free_bytes, ROUND(SUM(df.bytes)/ SUM(df.max_bytes)* 100, 2) AS used_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_name, Y.type AS TYPE, SUM(Y.bytes) AS bytes, SUM(Y.max_bytes) AS max_bytes, MAX(NVL(Y.free_bytes, 0)) AS FREE, ROUND(SUM(Y.bytes)/ SUM(Y.max_bytes)* 100, 2) AS used_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;

Comment by Alexey Pustovalov [ 2020 Oct 21 ]

Thank you! May I ask you update Oracle client libs on Zabbix side. For example, try to use 19 version. and then check again. Anyway, I think if sqlplus is working fine, probably some issue with client libs.

[ZBX-18514] Item "Get tablespaces stats" - does not work Created: 2020 Oct 16 Updated: 2020 Dec 15 Resolved: 2020 Dec 15
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Templates (T)
Affects Version/s:	None
Fix Version/s:	None

[ZBX-18514] Item "Get tablespaces stats" - does not work Created: 2020 Oct 16 Updated: 2020 Dec 15 Resolved: 2020 Dec 15