Oracle Enterprise Manager - 13c Reporting from Management Views

In this blog I discuss on how to extract data from OEM management views. 

I am discussing a task of extracting SGA / PGA monitoring data from OEM views

There 3 key views to keep eye on 
1. MGMT$METRIC_CURRENT - Keeps record of current values
2. MGMT$METRIC_HOURLY - Hourly records
3. MGMT$METRIC_DAILY - Aggregated Daily Records

You can get  more information on views from below - https://docs.oracle.com/cd/E63000_01/EMVWS/monview.htm#EMVWS12357

So to extract data from OEM view I use the below query to get my current SGA / PGA data

col DB_NAME  for a15
col SGA_Size_MB for a18
col value for a15
col DATA_COLLECTED for a30
break on DB_NAME ON DATA_COLLECTED skip 1

SELECT TARGET_NAME AS DB_NAME, COLUMN_LABEL AS METRIC_NAME, 
VALUE, to_char(COLLECTION_TIMESTAMP, 'dd-mm-yyyy hh24:mi') as DATA_COLLECTED
FROM MGMT$METRIC_CURRENT
WHERE 
TARGET_TYPE = 'oracle_database' AND
METRIC_NAME = 'memory_usage_sga_pga' AND
METRIC_LABEL = 'SGA and PGA usage' AND 
COLUMN_LABEL in  ('SGA Size(MB)' , 'PGA Total(MB)')
ORDER BY 
DB_NAME, METRIC_NAME;