Idea Details

SQL Statement query - create new metric from custom sql query

Last activity 12-17-2016 12:45 PM
Anon Anon's profile image
09-03-2015 01:41 PM


I need to be able to create an addtional metric from a custom metric query.  An example would be the Alert_status value calculated in the query below

 

 

SELECT tablespace_name,


       ROUND (total_size) total_size,


       ROUND (pct_free) free_percent,


       ROUND (free_gb) free_gb,


       (CASE


           WHEN total_size > 100 AND free_GB < 25 THEN '0'


           WHEN total_size < 100 AND pct_free < 5 THEN '0'


           ELSE '1'


        END)


          Alert_status


  FROM (  SELECT df.tablespace_name,


                 df.max_bytes / 1024 / 1024 / 1024 total_size,


                   (  (  (  DECODE (df.max_bytes,


                                    0, df.alloc_bytes,


                                    df.max_bytes)


                          / 1024


                          / 1024)


                       - (  (df.alloc_bytes / 1024 / 1024)


                          - NVL (fr.unused_alloc_bytes / 1024 / 1024, 0)))


                    / (  DECODE (df.max_bytes, 0, df.alloc_bytes, df.max_bytes)


                       / 1024


                       / 1024))


                 * 100


                    pct_free,


                   (  (df.max_bytes / 1024 / 1024)


                    - (  (df.alloc_bytes / 1024 / 1024)


                       - NVL (fr.unused_alloc_bytes / 1024 / 1024, 0)))


                 / 1024


                    free_GB


            FROM (  SELECT SUM (NVL (bytes, 0)) unused_alloc_bytes,


                           tablespace_name


                      FROM dba_free_space


                  GROUP BY tablespace_name) fr,


                 (  SELECT SUM (NVL (maxbytes, 0)) max_bytes,


                           SUM (bytes) alloc_bytes,


                             DECODE (SUM (NVL (maxbytes, 0)),


                                     0, SUM (NVL (bytes, 0)))


                           - SUM (NVL (bytes, 0))


                              unalloc_bytes,


                           tablespace_name


                      FROM dba_data_files


                  GROUP BY tablespace_name) df


           WHERE df.tablespace_name = fr.tablespace_name(+)


        ORDER BY df.tablespace_name, pct_free)


where tablespace_name not like '%UNDO%'