Tags

, , ,

Background

Metric entensions (ME) collect metrics that Oracle does not include in OEM 12c.

We need an accurate, up-to-date list of db links for internal reporting purposes.   OEM doesn’t collect it, so an ME is required.

Define the Data to Collect

You can get a list of DB links from the dba_db_links view in all 10g and later databases.

SELECT db_link AS link_name,

host AS link_to,

owner AS  link_owner,

username AS connect_as,

created

FROM sys.dba_db_links;

Map the Data to Display Names

ME defintion allows you to give meaningfujl names to the results when you define the data types.   The SQL query fields returned are mapped to Display Names like this:

link_name       Link Name  (KEY column)

link_to            Link Target

link_owner      Link Owner

connect_as    Connect As

created          Creation Date

Displaying the Results

When ME values are collected they end up in sysman.gc$metric_str_values_latest view.

The link_name appears in the query as key_part_1, the Display Names are returned in the metric_column_labels field, and the value for the other fields appear in the value column.  The metric_group_id is, of course, unique to my environment.

SELECT a.VALUE AS link_target,
a.entity_name,
a.key_part_1 AS link_name,
b.VALUE AS connect_as,
c.VALUE AS link_owner,
d.VALUE AS creation_date
FROM sysman.gc$metric_str_values_latest a,
sysman.gc$metric_str_values_latest b,
sysman.gc$metric_str_values_latest c,
sysman.gc$metric_str_values_latest d
WHERE a.metric_group_id = ‘5401’
AND b.metric_group_id = ‘5401’
AND c.metric_group_id = ‘5401’
AND d.metric_group_id = ‘5401’
AND a.metric_column_label = ‘Link Target’
AND b.metric_column_label = ‘Connect As’
AND c.metric_column_label = ‘Link Owner’
AND d.metric_column_label = ‘Creation Date’
AND a.entity_name = b.entity_name
AND a.key_part_1 = b.key_part_1
AND b.entity_name = c.entity_name
AND b.key_part_1 = c.key_part_1
AND c.entity_name = d.entity_name
AND c.key_part_1 = d.key_part_1
ORDER BY a.VALUE, a.entity_name;