Our customer has asked us to create custom checkpoints for Processes, Sessions and Tablespace as they feel the default does not provide sufficient information. The processes and sessions queries all produce a 1 row result and we can put that into a list designer dashboard.
The tablespace query produces a multiline result:
***********************
select total.ts tablespace,
total.mb total_mb,
NVL(total.mb - free.mb,total.mb) used_mb,
NVL(free.mb,0) free_mb,
DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used
from
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
dba_tablespaces dbat
where total.ts=free.ts(+)
and total.ts=dbat.tablespace_name
UNION ALL
select sh.tablespace_name,
SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
SUM(sh.bytes_used)/1024/1024 used_mb,
SUM(sh.bytes_free)/1024/1024 free_mb,
ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 1
***********************
How can we display this result in a list designer dashboard?
The Target/Host column also creates issues cause we cant display the tablespace column, when hosts are selected we can only display server names and when targets selected and configured it only displays the database names.