, ,

Pulling a list of database names from the sysman schema where target_type=’oracle_database’ often results in a mixture of database names and RAC instance names.

You can simplify the results by using a combination of CASE, INST, and SUBSTR functions like this:

SELECT CASE INSTR ( cm_target_name, ‘_’ )
WHEN 0 THEN cm_target_name
ELSE SUBSTR ( cm_target_name, 0, INSTR ( cm_target_name, ‘_’ ) – 1 )
END database_name,
FROM sysman.cm$em_tprops_ecm_view
WHERE cm_target_type = ‘oracle_database’
AND property_name = ‘orcl_gtp_lifecycle_status’
ORDER BY cm_target_name;

That’s a zero in the the ‘WHEN’ clause.