EM Grid Tables Link Gives Java.Sql.Sqlsyntaxerrorexception:ORA-00942: Table Or View Does Not Exist (Doc ID 1322943.1)

Last updated on NOVEMBER 07, 2016

Applies to:

Enterprise Manager for Oracle Database - Version 11.1.0.7 and later
Information in this document applies to any platform.
***Checked for relevance on 08-Nov-2016***

Symptoms

Customer is trying to create a read-only user for monitoring purposes and to connect to Enterprise Manager.

Newly created user gets Java.sql.SQLSyntaxErrorException:ORA-00942: table or view does not exist in Enterprise Manager -> Database -> Schema tab -> tables link

1) Create user user1 in Grid Control with monitor only privileges
2) Create user user1 on target database test1 to have read only privileges on scott.emp table
3) Log into Grid Control as user1 and then log into target database server as test1 user (tried to work with schema tab to see if I can see all data from scott.emp table - this did not work, also tried to execute a query from sql work sheet - select * from scott.emp - this did not work either)
4) Getting Error - Java.sql.SQLSyntaxErrorException:ORA-00942: table or view does not exist
5) On target server TEST1 database I can login as user1 and run a query select * from scott.emp - I get all my results on database level on the server
6) Through 'Execute SQL' sheet, able to see the results for 'select * from scott.emp'.
7) Create a new table in 'USER1' user and still not able to see through 'Schema' tab in Grid Control

Changes

User is newly created with CONNECT, RESOURCE role at the database level.
Also proper select on the tables is already given like GRANT SELECT ON SCOTT.EMP TO USER1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms