Underscores make a difference

I was setting up my environment to test out the examples in the book “Expert Oracle Database Architecture“. I started to set up runstats, a tool that measures the elapsed time, system statistics and latching. To use runstats, I (scott/tiger) needed access to some v$ tables (a.k.a. magic dynamic performance tables). On page xxxii of the book, it says:

… you can have SELECT on V$STATNAME, V$MYSTAT and V$LATCH granted directly to you…

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(*)
----------
       248

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

Back to runstats, it turns out that I also needed access to v$timer. To summarize, logged in as a “sys as sysdba”, here are the grants that I had to give to scott to make sure that scott could successfully execute runstats.sql:

grant select on v_$statname to scott;
grant select on v_$mystat to scott;
grant select on v_$latch to scott;
grant select on v_$timer to scott;

Possibly related:

Tagged | Post a Comment