Hello support,
I want to ask you how to process a result from a sql query which retrieve all tablespace name which occupation like this :
TABLESPACE_NAME SIZE(MB) USED(MB) MAX_EXT PCT_MAX_SIZE
------------------------------ ---------- ---------- ---------- ------------
tbs_1 411133.94 391752.13 430077.89 91
tbs_2 30720 18890 20480 92
The query returns all tablespaces where there occupation exceed 90% (several rows).
I would like to know how to proceed in prtg to deal with all the rows of the results and retrieve messages like :
'warning the tablespace tbs_1 is 91% full' 'warning the tablespace tbs_2 is 92% full'
Thank you very much for your help.
Regards.
Loubia
Article Comments
Hello Florian,
thank you a lot for your reply.
I'm new to PRTG, so I would like to ask you if there is a procedure or a tutorial for this type of needs which is very helpful for all the organisations which hosts Oracle databases with lots of tablespaces ?
We cannot create a channel for every tablespace per database (hundreds of tablespaces per instance) with the default oracle sensors.
I did not find a clear and detailed answer to this type of need in the forum despite the various posts on this subject.
Could you please help us ?
thank you very much,
kind regards
Sep, 2020 - Permalink
Hello,
Monitoring all the tablespaces with the Oracle Tablespace sensor is possible, when selecting tablespaces in the list, PRTG wil automatically create a sensor for each of them. Then, you can increase the scanning interval to 5 minutes or more (the higher, the better).
The manual of the sensor is here: https://www.paessler.com/manuals/prtg/oracle_tablespace_sensor
- Pros: Easier and natively supported in PRTG
- Cons: Creates a lot of sensors / Database
However, according to your need, I would not recommend to go that way. Indeed, you can develop a small custom script which executes the SQL query to get the list of tablespaces (including their size). Then, you only have to process the data and return the number of tablespaces whose size has exceeded thresholds you have choosen, as well as their name.
- Pros: You get only the information you need / 1 sensor only per Database
- Cons: Requires development skills
I'm afraid that we do not have any reference on similar scripts and can't provide so much support about it. However, we are happy to help regarding the implementation of the script with PRTG.
There is different ways to return data in PRTG via a custom script (depending on the type, basic or advanced), which are explained in this manual: https://www.paessler.com/manuals/prtg/custom_sensors#advanced_sensors
Kind regards.
Sep, 2020 - Permalink
Hello Florian,
thank you for your reply and explanations,
The SQL script is ready and returns the good values from sqlplus about tablespaces which exceed 90% (tablespace_name, occupation) so the development part is done and I use sqlsensor to execute the query.
the script result :
TABLESPACE_NAME PCT_MAX_SIZE ------------------------------ ------------ tbs_1 91 tbs_2 92
I just need some help to treat the data with implementing threshold if it's possible and the parameters on the sensors to use because i'm facing some issues like :
Id 3: "tbs_1" [System.String] value can not be parsed. Please keep in mind that any date or time formats need to use the "EN-US" format.
thank you very much Florian
Sep, 2020 - Permalink
Hello Loubia,
Thank you for your message.
Regarding what you would like to do, I would recommend to write a custom script and execute it with one of the EXE custom sensors. The script should return the total number of tablespace where the occupation exceed 90% in a channel, and displays the name of the corresponding tablespaces in the text message of the sensor.
You can find the manuals of the EXE sensors here: https://www.paessler.com/manuals/prtg/exe_script_sensor https://www.paessler.com/manuals/prtg/exe_script_advanced_sensor
The manual about Custom sensors shows the structure to follow when sending data in PRTG.
If you have further questions, don't hesitate.
Kind regards.
Sep, 2020 - Permalink