Toad Database Monitor - you do not have access to the required views to use the database monitor in this connection

Toad for Oracle
1

Hello everyone,

I would kindly ask for you help.

I have Toad for Oracle version 12.0.1, I have 2 databases, there I can do on both various tasks like compare object, session browser, but I have a problem that on one database I cannot start database monitor which gives the following error - you do not have access to the required views to use the database monitor in this connection.

According the documentation there some synonyms which are needed for the function to work:

you do not have access to the required views to use the database monitor in this connection

Database Monitor
The following public synonyms must be present.

v$sysstat

v$system_event

v$session

v$librarycache

x$ksllt

v$sgastat

I could find all besides x$ksllt, but this is missing also on the database there I can start database monitor.

IM using the system user for the connection, but if I try I application user it works.

the grants for the system users are as follows:

– 5 Roles for SYSTEM
GRANT APPL_OWNER TO SYSTEM WITH ADMIN OPTION;
GRANT AQ_ADMINISTRATOR_ROLE TO SYSTEM WITH ADMIN OPTION;
GRANT DBA TO SYSTEM WITH ADMIN OPTION;
GRANT MGMT_USER TO SYSTEM;
GRANT PLME_RW TO SYSTEM WITH ADMIN OPTION;
ALTER USER SYSTEM DEFAULT ROLE ALL;
– 5 System Privileges for SYSTEM
GRANT CREATE MATERIALIZED VIEW TO SYSTEM;
GRANT CREATE TABLE TO SYSTEM;
GRANT GLOBAL QUERY REWRITE TO SYSTEM;
GRANT SELECT ANY TABLE TO SYSTEM;
GRANT UNLIMITED TABLESPACE TO SYSTEM WITH ADMIN OPTION;

Thank you in advance

Martin

2

To see what queries Toad is running, you can always go to Main Menu -> Database -> Spool SQL -> Spool to Screen.

When I do that and start up the Database Monitor, then look at the queries, this is the list I get:

V$SESSION

V$LIBRARYCACHE

V$SYSSTAT

v$event_name

v$system_event

v$sys_time_model

v$sysstat

V$LATCH

V$SGASTAT

We don’t use the X$ views at all in Toad, since they are only visible to SYS and cannot be granted.

I see you have “SELECT ANY TABLE” granted to system - this refers to application tables, not data dictionary views. You can either explicitly grant the selected views to SYSTEM, or grant SELECT ANY DICTIONARY and then it should work.

3

Hello John,

Thank you very much for the fast response.

I have grant executed SELECT ANY DICTIONARY to system;

and also

grant select on SYS.V_$SESSION to SYSTEM;

grant select on SYS.V_$LIBRARYCACHE to SYSTEM;

grant select on SYS.V_$SYSSTAT to SYSTEM;

grant select on SYS.V_$event_name to SYSTEM;

grant select on SYS.V_$system_event to SYSTEM;

grant select on SYS.V_$sys_time_model to SYSTEM;

grant select on SYS.V_$sysstat to SYSTEM;

grant select on SYS.V_$LATCH to SYSTEM;

grant select on SYS.V_$SGASTATto SYSTEM;

still to change :frowning:

4

Hm…OK, a couple of thoughts:

  1. You may need to disconnect and reconnect Toad if you haven’t done that.

  2. If you still have problems, turn on spool SQL as I described and locate the failing query.

5

that was it, disconnected, connected and now it works, that you so much John

Martin