Oracle Tablespace sensor query

Here's the underlying query for the Oracle Tablespace sensor:

SELECT MAX(
  CASE
    WHEN t.online_status = 'ONLINE'
    THEN 0
    WHEN t.online_status = 'SYSTEM'
    THEN 1
    WHEN t.online_status = 'RECOVER'
    THEN 2
    WHEN t.online_status = 'SYSOFF'
    THEN 3
    WHEN t.online_status = 'OFFLINE'
    THEN 4
    ELSE 100
  END) online_status,
  MAX(
  CASE
    WHEN t.status = 'AVAILABLE'
    THEN 0
    WHEN t.status = 'INVALID'
    THEN 1
    ELSE 100
  END ) status,
  SUM(t.maxbytes)  - SUM(t.bytes) FREE,
  TRUNC(100        * (SUM(t.maxbytes) - SUM(t.bytes)) / SUM(t.maxbytes), 12) free_perc,
  SUM(t.maxblocks) - SUM(t.blocks) blocks_free,
  TRUNC(100        * (SUM(t.maxblocks) - SUM(t.blocks)) / SUM(t.maxblocks), 12) blocks_free_perc,
  SUM(t.bytes) used,
  SUM(t.disk_bytes) disk_used
FROM
  (SELECT a.tablespace_name,
    a.file_name,
    a.online_status,
    a.status,
    CASE
      WHEN a.AUTOEXTENSIBLE = 'NO'
      THEN a.BYTES
      ELSE a.MAXBYTES
    END maxbytes,
    (SELECT a.BYTES - NVL(SUM(b.BYTES), 0)
    FROM dba_free_space b
    WHERE b.FILE_ID = a.FILE_ID
    ) bytes,
    a.BYTES disk_bytes,
    CASE
      WHEN a.AUTOEXTENSIBLE = 'NO'
      THEN a.BLOCKS
      ELSE a.MAXBLOCKS
    END maxblocks,
    (SELECT a.BLOCKS - NVL(SUM(b.BLOCKS), 0)
    FROM dba_free_space b
    WHERE b.FILE_ID = a.FILE_ID
    ) blocks
  FROM dba_data_files a
  ) t
WHERE t.tablespace_name = 'TABLESPACENAME';

Best Regards,
Luciano Lingnau [Paessler Support]

Hi Support,

I'm using 19.2.50.2842 version.

Is this query still valid ?

Yes the query is still valid.

Kind regards,
Sasa Ignjatovic, Tech Support Team

What is the timeout in seconds for this query? Can it be increased?

We have added multiple Oracle Tablespace sensors, but someone of them time out. When the same query is run on the server locally, it takes more than a minute to display the results.

Hello Viswanathan,

The timeout for the Oracle Tablespace sensor is 5000ms. You can increase the metascan timeout if needed, for instructions on how to do this, please see this KB article.

Kind regards,
Sasa Ignjatovic, Tech Support Team