Logged in as scott:

scott@EDDEV> select * from v$mystat;

select * from v$mystat

ERROR at line 1:

ORA-00942: table or view does not exist

Obviously, scott did not have the privilege to select from v$mystat. So, I went ahead and logged in as “sys as sysdba” and issued the following grant:

sys@EDDEV> grant select on v$mystat to scott;

grant select on v$mystat to scott

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

OOPS! What’s the problem here?!

Whenever I face a problem that I do not know how to solve, I always search for a solution first before starting to ask questions. So, a simple search for “ORA-02030″ on AskTom revealed that I needed to use v_$maystat instead of v$mystat:

sys@EDDEV> grant select on v_$mystat to scott;

Grant succeeded.

Logged in as scott:

scott@EDDEV> select count(*) from v$mystat;

  COUNT(*)

----------

It worked. Now scott has access to v$mystat. So, if you want to grant a privilege on a v$ object, make sure you use the corresponding v_$ object. Note that v$ objects are synonyms and v_$ objects are views:

sys@EDDEV> select object_name, object_type

  2  from all_objects

  3  where object_name = 'V$LATCH'

  4  /

OBJECT_NAME                    OBJECT_TYPE

------------------------------ ------------------

V$LATCH                        SYNONYM

sys@EDDEV> c/V$/V_$

 3* where object_name = 'V_$LATCH'

sys@EDDEV> l

  1  select object_name, object_type

  2  from all_objects

  3* where object_name = 'V_$LATCH'

sys@EDDEV> /

OBJECT_NAME                    OBJECT_TYPE

------------------------------ ------------------

V_$LATCH                       VIEW

v$mystat does not exist