New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.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 - Freespace % on all Instances 0%

Votes:

0

Your Vote:

Up

Down

Hi all,

we are very confused, why on all Oracle Instances the Freespace % value is 0% at same time on 8 different Instances. How can we chack if the Oracle Tablespace sensor is working well??

What is the SQL-Statemant for this check?

Thanks for reply.

freespace oracle oracle-tablespace-sensor

Created on Jan 14, 2019 9:07:35 AM by  henking (0) 1



20 Replies

Votes:

0

Your Vote:

Up

Down

The actual query behind the sensor is the following: SELECT DISTINCT tablespace_name, file_id, bytes, blocks FROM dba_free_space ORDER BY file_id;

...does the result add up?


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 14, 2019 11:19:00 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Thanks for the SQL. When i check this with SQLv2.exe, the result is:

0: "SYSTEM" [System.String] value can not be parsed. Please Keep in mind that any date or time formats Need to use the "EN-US" Format.

Any ideas??

Created on Jan 14, 2019 11:52:58 AM by  henking (0) 1



Votes:

0

Your Vote:

Up

Down

You'll need to use a SQL client to check that, the SQLv2.exe won't work here :(


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 14, 2019 1:02:34 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hi Stephan,

thanks for your hint. SQL is working fine now with SQLplus. How does PRTG interpret this data?

Please have a look at the following Screenshot of my Oracle Schema (free space, free blocks)

https://drive.google.com/file/d/1x_1xOpLBxt38VeQGSMDiklSkAgKCjWye/view?usp=sharing

I have no idea why free space and blocks are 0%

Created on Jan 15, 2019 9:05:55 AM by  henking (0) 1



Votes:

0

Your Vote:

Up

Down

Me neither - Could you provide me with the actual output of the SQL command within SQLplus?


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 15, 2019 9:15:52 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Okay, what table was the screenshot from? :)

Created on Jan 15, 2019 9:31:16 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

The Screenshot was from DATA1 Tablespace. But ALL other Tablespaces has the same Message.

Created on Jan 15, 2019 9:42:48 AM by  henking (0) 1



Votes:

0

Your Vote:

Up

Down

Alright, I'll have this checked by our devs :) I'll be in touch, it may take a day or two.


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 15, 2019 1:45:10 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

I'll need the output of the following query as well:

SELECT
  tablespace_name,
  file_id,
  online_status,
  autoextensible,
  bytes,
  user_bytes,
  maxbytes,
  blocks,
  user_blocks,
  maxblocks,
  increment_by
FROM
  dba_data_files
ORDER BY
  file_id;

Thanks! :)


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 15, 2019 4:12:09 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Forwarded to the developer, I think I'll hear from him quickly :)


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 16, 2019 8:40:52 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Sorry, we seemingly need the whole package to debug this :( Please run the following queries:

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
WHERE file_id = 4;
SELECT DISTINCT tablespace_name,
  file_id,
  bytes,
  blocks
FROM dba_free_space
WHERE file_id = 4
ORDER BY file_id;

The queries should be syntactically valid :) Thanks in advance.


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 16, 2019 1:33:19 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hi all,

do you have any update for me?

Thx Mathias Henking

Created on Jan 21, 2019 8:06:15 AM by  henking (0) 1



Votes:

0

Your Vote:

Up

Down

Not yet, I'll keep you posted :)


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 21, 2019 8:14:14 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hello,

When can we expect the result of the analysis? We operate our Oracle instances in "blind flight"!! Thx for reply.

Mathias

Created on Jan 28, 2019 9:20:20 AM by  henking (0) 1



Votes:

0

Your Vote:

Up

Down

Sorry, seemingly my last reply in that thread got lost somehow. Please excuse the delay. Seemingly, the calculation of the sensor is indeed correct and we're running into a rounding issue. The following results were found during our tests:

99,976539600629   - displayed correctly. 
99,99694824073231 - Your instance, displayed as 0.

In order to prove that claim, please enable the Write Result To Disk within the sensor settings and open up C:\ProgramData\Paessler\PRTG Network Monitor\Logs (Sensors). There should be a file with the ID of the sensor (which is disclosed in the URL). Provide it as usual and I'll forward it to development.

Again, allow me to apologize for the rather long time this takes to resolve.


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 28, 2019 9:41:14 AM by  Stephan Linke [Paessler Support]

Last change on Jan 28, 2019 9:42:02 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hi Stephan, is the setting to activate "Write to Disk" in Oracle Tablespace sensor hidden somewhere?

Created on Jan 29, 2019 8:30:30 AM by  henking (0) 1



Votes:

0

Your Vote:

Up

Down

No, it's not hidden - the Sensor doesn't have the setting, contrary to my beliefs. I'm waiting for the developer to provide the corresponding command line parameters or any other additional steps. I'll update as soon as I hear back from him.


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 29, 2019 10:36:02 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Here's the query - sorry, that took too long :(

SQLv2.exe "-dbuser=DB_USER" "-dbpass=DB_PASSWORD" "-host=DB_HOST" "-dbauth=1" "-database=SID=DB_SID" "-timeout=60" "-server=Oracle" "-handledbnull=1" "-querytype=ReadData" "-transaction=noTransaction" "-selectvalue=ColumnName" "-channelids=3|4|5|6|7|8|9|10" "-selectors=online_status|status|free|free_perc|blocks_free|blocks_free_perc|used|disk_used" "-isdif=0|0|0|0|0|0|0|0" "-query=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 = 'USERS'"

Navigate to C:\Program Files(x86)\PRTG Network Monitor\Sensor System\ via cmd and execute it, then provide us with the output. Thanks!


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Feb 1, 2019 7:08:37 PM by  Stephan Linke [Paessler Support]

Last change on Feb 1, 2019 7:09:43 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Thanks! I'll forward it :)


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Feb 4, 2019 8:54:55 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

There should also be some XML output - was this missing in the KB post due to the copy&paste?


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Feb 4, 2019 12:04:41 PM by  Stephan Linke [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.