What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

Oracle Tablespace sensor query

Votes:

0

Your Vote:

Up

Down

Hello,

How can ifind out the exact query executed by the sensor?

Thanks, Yaron

oracle oracle-rac oracle-tablespace-sensor

Created on Aug 1, 2016 3:27:39 PM by  YEidelman (2) 2



5 Replies

Votes:

0

Your Vote:

Up

Down

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]

Created on Dec 21, 2017 2:49:28 PM by  Luciano Lingnau [Paessler]



Votes:

0

Your Vote:

Up

Down

Hi Support,

I'm using 19.2.50.2842 version.

Is this query still valid ?

Created on Nov 12, 2019 5:42:31 AM by  Jonathan_Jung (5) 2



Votes:

0

Your Vote:

Up

Down

Yes the query is still valid.


Kind regards,
Sasa Ignjatovic, Tech Support Team

Created on Nov 12, 2019 9:07:18 AM by  Sasa Ignjatovic [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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.

Created on Mar 16, 2022 7:28:47 AM by  Viswanathan Ramanathan (20)



Votes:

0

Your Vote:

Up

Down

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

Created on Mar 21, 2022 6:06:16 AM by  Sasa Ignjatovic [Paessler Support]



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.