Tales From A Lazy Fat DBA

Hi Guys,

Recently while working on a custom OEM 13c Information Publisher Report to automate monitoring production database I’d encountered an error which says “Error rendering element. Exception: ORA-00942: table or view does not exist” while previewing an underlying custom SQL. The SQL was a join between multiple OEM repository views (mgmt$target_properties, gc_metric_values, mgmt_targets, mgmt$db_datafiles, mgmt$metric_current, mgmt$db_init_params etc.) and throws an error whenever I tried to run the code. Interestingly the SQL working absolutely when I ran it directly on the OEM repository database, but failed when I tried to call the same SQL via Information report on OEM.

I was confused as I was connected with the SYSMAN, passing the same SQL which I’d tested on OMS database directly, all OMS logs were clean, then why its failing for Information Reports. Though from error it was evident that those objects belongs to another schema but I was already connected using SYSMAN in OEM …

I did a quick test to verify if this the reason. As a test ran this simple query as a report first from the EM reports.

select target_name,target_type from mgmt_targets where target_name ='FATDB';

Next, I tried from the repo and granted SELECT ON privilege on mgmt_target view to report owner MGMT_VIEW, and re ran the query, and this time it worked 🙂

SQL> Grant SELECT ON mgmt_targets to MGMT_VIEW;

So, the reason was, the Information Publisher reports uses the MGMT_VIEW user to generate reports and MGMT_VIEW has privileges to access the MGMT$ views only. This is an account used by Oracle Enterprise Manager Database Control for reports. As my custom SQL referenced multiple non-MGMT$ views i.e. gc_metric_values & mgmt_targets and hence failed to recognize them and needs permissions to read data from those views.

Hope It Helped!
Prashant Dixit