SQL & PL/SQL

Oracle Enterprise Manager - 13.5 RU19

Oracle Management Repository - 19.20 on Exadata X6.

I am using multiple queries to get metric data from mgmt views.

1. Gathering cpu and memory details of all database targets
2. Gathering zfs project storage details.

Objective is to

1. From the query I am running to get database resource details, I am looking to print with the following condition - I now want to check the following condition..
   if the tenant is EBS, then based on the environment groupings, add the cpu values and report as sum.
   For example, tenant is EBS, and there are 2 DBs under TST1, so add 8 and 8.
   Another example, tenant is CFX, and there are 2 DBs under TST2, so add cpu 6 and 6.
   I want the tenant to be saved in a variable and not hardcoded.
   Print the sum value separately.'
2. I want to merge query 1 and 2 based on the Tenant and print the output.

Attaching the code and the current results of that code.

Result of Query 1:

TENANT TENANT_ID APPID DB ENVIRONMENT LOCATION CPU MEMORY(SGA) MEMORY(PGA)
----------------------------------------------------------------------------------------------------------------
CFX 4 A00295 CFXD3_CFXD31 TST1 RCC - Test 2 24 GB 16 GB
CFX 4 A00295 CFXU1_CFXU11 TST2 WSDC - Test 6 24 GB 16 GB
CFX 4 A00295 CFXU1_CFXU12 TST2 WSDC - Test 6 24 GB 16 GB
EBS 19 A0009A EBSD3_EBSD311 TST1 RCC - Test 8 16 GB 16 GB
EBS 19 A0009A EBSD4_EBSD411 TST1 RCC - Test 8 16 GB 16 GB
EBS 19 A0009A EBST1_EBST111 TST2 WSDC - Test 10 16 GB 30 GB

Result of Query 2:

TARGET_NAME TENANT TOTAL ALLOCATED (GB)
-----------------------------------------------
ohsr9301 ACM 1789.34
ohsr9302 EBS 1715.75
ohsr9301 CFX 46584.25
ohsr9302 CSH 31125.15
ohsr9301 DET 30611.67

SELECT 
    COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_department' THEN tp.property_value END), '-') AS TENANT,
    COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_cost_center' THEN tp.property_value END), '-') AS TENANT_ID,
    COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_line_of_bus' THEN tp.property_value END), '-') AS APPID,
    tp.target_name AS DB,
    COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_comment' THEN tp.property_value END), '-') AS ENVIRONMENT,
    COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_location' AND tp.property_value IN ('RCC - Test', 'WSDC - Test') THEN tp.property_value END), '-') AS LOCATION,
    COALESCE(MAX(CASE WHEN pp.name = 'cpu_count' THEN pp.value END), '-') AS CPU,
    COALESCE(MAX(CASE WHEN pp.name = 'sga_max_size' THEN ROUND(pp.value / POWER(1024, 3), 2) || ' GB' END), '-') AS "MEMORY(SGA)",
    COALESCE(MAX(CASE WHEN pp.name = 'pga_aggregate_limit' THEN ROUND(pp.value / POWER(1024, 3), 2) || ' GB' END), '-') AS "MEMORY(PGA)"
FROM 
    mgmt$target_properties tp
LEFT JOIN
    MGMT$DB_INIT_PARAMS pp ON tp.target_name = pp.target_name
WHERE 
    tp.property_name IN ('orcl_gtp_department', 'orcl_gtp_cost_center', 'orcl_gtp_line_of_bus', 'orcl_gtp_comment')
    OR (tp.property_name = 'orcl_gtp_location' AND tp.property_value IN ('RCC - Test', 'WSDC - Test'))
GROUP BY 
    tp.target_name
HAVING
    MAX(CASE WHEN tp.property_name = 'orcl_gtp_location' AND tp.property_value IN ('RCC - Test', 'WSDC - Test') THEN 1 ELSE 0 END) = 1
ORDER BY 
    TENANT;

SELECT 
    SUBSTR(t.target_name, 5) AS TARGET_NAME,
    SUBSTR(m1.key_value, INSTR(m1.key_value, ':') + 1, INSTR(m1.key_value, '_') - INSTR(m1.key_value, ':') - 1) AS TENANT,
    ROUND(m1.value_maximum, 2) AS "TOTAL ALLOCATED (GB)"
FROM 
    sysman.mgmt_metrics_1day m1
JOIN 
    sysman.mgmt_targets t ON t.target_guid = m1.target_guid
WHERE 
    metric_guid = 'ED3DEC27B1F3F6BAFD22DA42DE888731'
AND 
    t.target_name LIKE '%ohsr930%'
    AND m1.key_value like '%dbbackup'
AND 
    m1.rollup_timestamp > SYSDATE - 3
ORDER BY 
    2, 1;