How to grant privileges on V$ tables??

Hi Data lovers,

I was testing something in my 11.2.0.3 database today and as part of that i created a user and he needs select on some of the v$views.

i tried granting privileges directly through the below command

SQL> grant select on v$archived_log to csm;
grant select on v$archived_log to csm
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The error message looks so weird for me. The “V$” are not views really?

After going through the documentation i found a fact that they’re called as views while referring to them but they’re actually synonyms. So the error message is legitimate but not weird as i thought.

So i accomplished my purpose by doing this

SQL> grant select on v_$archived_log to csm;

Grant succeeded.

What’s this “V_$” thing? The actual V$ views have the names like V_$ARCHIVED_LOG, V_$SESSION etc…the V$ representation is just a synonym of the original view.

So grants on the original view solved the problem.

p.s. I’m still not sure why Oracle has synonyms with these names instead of just leaving the view names intact and publishing them tot the world…Then we might be calling them as V_$ views instead of v$ views…Any historical reason, probably :)

CSM

About these ads

One Response to How to grant privileges on V$ tables??

  1. csmdba says:

    One guy, whom i met in some online forum, was saying that this is to help us in avoiding the underscore while typing their names. We can just say V$SESSION instead of V_$SESSION…I’m not sure it’s one of the reasons or not but some input/interpretation at least :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 332 other followers

%d bloggers like this: