OEM SQL

DB Growth  Month wise....

  

=================================================

SELECT TARGET_NAME,

       decode(metric_column, 'spaceAllocated', 'AVERAGE_SIZE_GB', 'spaceUsed', 'AVERAGE_USED_GB'),

       month_timestamp,

       avg(value)

FROM

  (SELECT m.TARGET_NAME,

          m.metric_column AS metric_column,

          TO_DATE(TO_CHAR(m.rollup_timestamp, 'YYYY-MM'), 'YYYY-MM') AS month_timestamp,

          sum(m.average/1024) AS value

   FROM mgmt$metric_daily m,

        mgmt$target_type t

   WHERE (t.target_type='rac_database'

          OR t.target_type='oracle_pdb'

          OR (t.target_type='oracle_database'

              AND t.TYPE_QUALIFIER3 != 'RACINST'))

     AND m.target_guid=t.target_guid

     AND m.metric_guid=t.metric_guid

     AND (t.metric_name='tbspAllocation')

     AND (t.metric_column='spaceAllocated'

          OR t.metric_column='spaceUsed')

     AND m.rollup_timestamp > sysdate-360

   GROUP BY m.TARGET_NAME,

            m.metric_column,

            m.rollup_timestamp)

WHERE metric_column='spaceAllocated'

GROUP BY TARGET_NAME,

         metric_column,

         month_timestamp

ORDER BY TARGET_NAME,

         month_timestamp ASC,

         metric_column DESC;

=================================================

DB using filesystem as datafile;

 select distincT TARGET_NAME,(substr(file_name,1,instr(file_name,'/',-3)))AS DG_NAME ,HOST_NAME,TARGET_TYPE from MGMT$DB_DATAFILES where file_name like '/%'; 

======================================

 DB using filesystem as datafile;

  select distincT TARGET_NAME,(substr(file_name,1,instr(file_name,'/',-3)))AS DG_NAME ,HOST_NAME,TARGET_TYPE from MGMT$DB_DATAFILES where file_name like '/+'  ;

======================================

Archive DG with FRA threshold limit 

 select distinct v1.target_name, v1.key_value , v1.value/1024 "Size", v2.value/1024 "Available" ,v3.value "pct Available"

from

(select target_name, key_value,  value  from mgmt$metric_current

where

metric_name='Filesystems'

and metric_column ='size') v1,

(select target_name, key_value,  value   from mgmt$metric_current

where

metric_name='Filesystems'

and metric_column ='available') v2,

(select target_name, key_value,  value  from mgmt$metric_current

where

metric_name='Filesystems'

and metric_column ='pctAvailable') v3

where v1.key_value=v2.key_value

and v2.key_value=v3.key_value

order by v1.key_value

/

===========================================