New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

300.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

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

 

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 (0) 2



3 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 Support]



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 (0) 1



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]



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.