Re: Select access to v_$

Barbara Hiles wrote:
>
> This is probably going to be something really dumb - but....
> I can't do selects on v_$ tables or views or whatever they are. I can access
> the same v$ stuff. And I suppose that should be good enough - but I don't
> understand why I can't get the v_$ stuff.
> I have the dba role and I also setup the monitorer role (which specifically
> grants select access to a lot of the v_$ tables) and gave that to me and I
> still get table or view doesn't exist.
> If I log in as sys I can do the select.
> What is the catch? What am I missing?
>
> tia - barb.

Barb,

The "V_$...." objects are actually views owned by the user SYS. The "V$...." objects are public synonyms to these views. The DBA Role grants you access to the V_$.... views owned by SYS and the public synonym makes the fact that the base view is owned by SYS transparent to you the user.

For example:-  

SYS owns the view V_$SESSION.
There is a public synonym V$SESSION for the V_$SESSION view.

So when you do :-

        SQL> select * from V$SESSION;...you are actually accessing the V_$SESSION view through the
public synonym.
You cannot however do a...

         SQL> select * from V_$SESSION because V_$SESSION is not owned by you. To get around this ( if you must ) you could reference V_$SESSION as SYS.V_$SESSION. So you would do ...

        SQL> select * from sys.V_$SESSION;
One of the reasons SYNONYMS are used in ORACLE is to reference the object without specifying its owner.

The bottom line is whenever you do a SELECT .... from <object_name> (without referencing the object's owner)....ORACLE assumes one of the following:-

                i. You own the object...(could be a table, view or a private synonym) or

               ii. The object is a public synonym. If you want to know more about synonyms and database objects in general you can query any of the data dictionary views... e.g DBA_OBJECTS would have told you that V_$SESSION is a "view" owned by SYS, while V$SESSION is a public synonym. To find about synonyms and their base tables look up DBA_SYNONYMS.

Hope this helped...
Naari. Received on Wed Dec 04 1996 - 00:00:00 CST