I'm facing the same "issue". The first problem here is that the used "Oracle User Experince Monitor" only allows one set of Statisic/Message so you may add as many of the "Used Space in Tablespace (%)" componets as needed and change the tablespace name in the query.
The second problem is that you may have more the ten table spaces in your database so that you cannot use a simple script monitor to check all tablespaces you have. At the moment I'm thinking of a monitor that checks all tablesapces and sends some kind of html-table back that shows all "red", "yellow" and "green" tablespaces and regarding the overall status something like "critical" or "warning". The disadvantage is that you can't collect statisitcs over the tablespaces. The second approach might be an alert that checks the amount of tablespaces actual on the database, compares them with the monitred one and add/remove automatically the monitors but I'm not sure if that can be done with SWQL as you can do it on the GUI.
Also a good idea is to vote for Oracle AppInsight Monitor: AppInsight for Oracle
Also, if I come across any sort of temporary work around I'll post it here.
Yeaop. This was a bear to configure for a large client. I had to make like 300 of these CMs.
OpsLogix was much simpler, and discovered all TS intelligently. I wish Solar was like this.
Add new component Oracle User Experience Monitor with the following sql query:
count(df.tablespace_name) "Nr of Tablespaces",
listagg(df.tablespace_name || ' - Total space: ' || df.TotalSpace ||'(MB); Used space: ' || tu.TotalUsedSpace || '(MB); Free Space: ' || (df.TotalSpace - tu.TotalUsedSpace)|| '(MB)' || ' ~ ' || (round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)))|| '%;', chr(10)) WITHIN GROUP (ORDER BY df.tablespace_name) "Free Space(MB and %)"
(select tablespace_name, sum(bytes)/1048576 TotalSpace
group by tablespace_name) df,
(select tablespace_name, sum(bytes)/1048576 TotalUsedSpace
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
df.totalspace >= 10 -- if total table space is greater that 10 mb
and (round(df.totalspace - tu.totalusedspace) <=5 -- if free space is less than 5 mb
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) < 20) --if free space is less than 20%
This way you can output all / some tablespaces and the statistics can be used for alerting. Enjoy!
This is awesome!
Just wondering how you would handle the alerting for each individual one?
My purpose was to alert on all tablespaces with issues, not really in a individual way. That can be done with the default sql script from Solarwinds, and you just replace the System with your tablespace name.
SELECT SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
GROUP BY tablespace_name) df
WHERE fs.tablespace_name = df.tablespace_name
AND fs.tablespace_name = 'SYSTEM' ---replace here
GROUP BY df.tablespace_name,df.bytes