I have a view that I created in OEM 12c owned by SYSMAN. The view is the following:
create or replace view chk_dbmango as
SELECT LOWER (a_size.target_name) target_name,
tphost.host_name,
tpba.business_area,
tpd.contact,
tpapp.application,
tpenv.environment,
ROUND (AVG (a_size.size_gb), 2) SIZE_GB,
ROUND (AVG (a_size.used_gb), 2) USED_GB,
ROUND (AVG (a_size.size_gb - a_size.used_gb), 2) FREE_GB,
ROUND (AVG ((a_size.used_gb * 100) / DECODE (a_size.size_gb, 0, 1, a_size.size_gb))) USED_PCT
FROM ( SELECT t.target_name,
t.target_guid,
SUM (d.tablespace_size) / 1024 / 1024 / 1024 size_gb,
SUM (d.tablespace_used_size) / 1024 / 1024 / 1024 used_gb
FROM sysman.mgmt$db_tablespaces d, sysman.mgmt$target_type t
WHERE (t.target_type = 'rac_database'
OR (t.target_type = 'oracle_database'
AND t.TYPE_QUALIFIER3 <> 'RACINST'))
AND d.target_name(+) = t.target_name
AND t.metric_name = 'tbspAllocation'
AND (t.metric_column = 'spaceAllocated')
GROUP BY t.target_name, t.target_guid, t.metric_column) a_size,
( SELECT property_value AS host_name,
target_guid
FROM sysman.mgmt$target_properties
WHERE property_name = 'MachineName') tphost,
( SELECT property_value AS business_area,
target_guid
FROM sysman.mgmt$target_properties
WHERE property_name = 'orcl_gtp_line_of_bus') tpba,
( SELECT property_value AS contact,
target_guid
FROM sysman.mgmt$target_properties
WHERE property_name = 'orcl_gtp_contact') tpd,
( SELECT property_value AS application,
target_guid
FROM sysman.mgmt$target_properties
WHERE property_name = 'orcl_gtp_comment') tpapp,
( SELECT property_value AS environment,
target_guid
FROM sysman.mgmt$target_properties
WHERE property_name = 'orcl_gtp_deployment_type') tpenv
WHERE a_size.target_guid = tphost.target_guid
AND a_size.target_guid = tpba.target_guid(+)
AND a_size.target_guid = tpd.target_guid(+)
AND a_size.target_guid = tpapp.target_guid(+)
AND a_size.target_guid = tpenv.target_guid(+)
GROUP BY a_size.target_name,
tphost.host_name,
tpba.business_area,
tpd.contact,
tpapp.application,
tpenv.environment
ORDER BY 1;
I granted SELECT on this view to user svcmango. When connected as svcmango and query the view, I get no rows selected. Connected as sysman I get 99 rows. There must be something simple that I have overlooked. Please help. Thanks.