AnsweredAssumed Answered

Oracle Probe custom tablespace Checkpoint

Question asked by RakeshKeshawZA on Jul 14, 2017
Latest reply on Jul 24, 2017 by Ryan Dunn

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


        (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


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.