Oracle Tablespace - Freespace % on all Instances 0%

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

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??

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

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%

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

Okay, what table was the screenshot from? :)

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

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

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

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

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

Hi all,

do you have any update for me?

Thx Mathias Henking

Not yet, I'll keep you posted :)

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

Kind regards,
Stephan Linke, Tech Support Team

Hello,

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

Mathias

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

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

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

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

Thanks! I'll forward it :)

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

Kind regards,
Stephan Linke, Tech Support Team

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