msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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;

Filed in Oracle, Tips on 04 Oct 05 | Tags:


Reader's Comments

  1. |

    Eddie — thanks, in the future you can drop me an email too. I file the errata and it’ll get subsequent prints fixed.

  2. |

    Tom, hopefully I won’t find any additional errata, but if I do I’ll make sure I e-mail you about it.

    Thank you for this great book.

  3. |

    Good stuff! My copy of the book is on its way. Lucky for me, an Amazon.com distribution center is down the road. Shame they don’t allow pick-ups.

  4. |

    […] One of the blogs I frequent has found an errata that you should check out before continuing with reading. […]

  5. |

    So tell me, how does this article know I linked to you? I’m ignorant on all blog technology it seems.

  6. |

    Tom,

    It looks like that your blog, like mine, is on wordpress. WordPress supports “pingbacks” and “trackbacks”. That means, whenever your post has a link to another blog, wordpress tries to send a ping to that other blog. If the linked blog supports pinging, your post will usually appear in the comment section as it did here.

    In wordpress there are two places where you can control this:

    1. WP admin –> Options –> Discussions: “Attempt to notify any Weblogs linked to from the article (slows down posting.)” and “Allow link notifications from other Weblogs (pingbacks and trackbacks.)”. I have them both checked on my blog.

    2. WP admin –> Write –> Write Post: “Allow Pings”. I also have this one checked by default.

    Hope this helps.

  7. |

    Cool.. I was wondering how in the world that happened. I guess I should read the fine manual, eh? :)