2013-10-31

List databases from OEM with pivoting PROPERTY NAME to columns

Environment: OEM12, Oracle database 11.2


SELECT * FROM 
   (SELECT T.TARGET_NAME, P1.PROPERTY_NAME, P1.PROPERTY_VALUE
    FROM SYSMAN.MGMT_TARGETS T, SYSMAN.MGMT_TARGET_PROPERTIES P1
    WHERE T.TARGET_TYPE='oracle_database'
    AND T.TARGET_GUID = P1.TARGET_GUID
    AND NOT EXISTS (SELECT 1 FROM MGMT_BLACKOUT_FLAT_TARGETS B WHERE T.TARGET_GUID = B.TARGET_GUID) -- excluding blackouts
    AND UPPER(T.TARGET_NAME) LIKE '%' -- filter by database name
    AND P1.PROPERTY_NAME IN ('DBVersion','VersionBanner','CPUCount','MachineName','orcl_gtp_lifecycle_status','OracleHome')
   )
PIVOT
(MAX(PROPERTY_VALUE) FOR PROPERTY_NAME IN ('DBVersion' ,'VersionBanner','CPUCount','MachineName','orcl_gtp_lifecycle_status' "Lifecycle",'OracleHome'))
ORDER BY 1;