OraFAQ Forum: Server Administration » difference between V

Home » RDBMS Server » Server Administration » difference between V_$ and V$ views

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

difference between V_$ and V$ views [message #143187]

Wed, 19 October 2005 07:20

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

Can anyone explains me the difference between V$ and V_$ views.
It looks same to me.
lets take the example of V$LOG and V_$LOG , both views returns same set of columns when described.

SQL> desc V$LOG
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 GROUP#                                             NUMBER
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 BYTES                                              NUMBER
 MEMBERS                                            NUMBER
 ARCHIVED                                           VARCHAR2(3)
 STATUS                                             VARCHAR2(16)
 FIRST_CHANGE#                                      NUMBER
 FIRST_TIME                                         DATE

SQL> desc V_$LOG
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 GROUP#                                             NUMBER
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 BYTES                                              NUMBER
 MEMBERS                                            NUMBER
 ARCHIVED                                           VARCHAR2(3)
 STATUS                                             VARCHAR2(16)
 FIRST_CHANGE#                                      NUMBER
 FIRST_TIME                                         DATE

thanks & regards,
tarun

Report message to a moderator

Re: difference between V_$ and V$ views [message #143193 is a reply to message #143187]

Wed, 19 October 2005 07:40

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

v_$ is a view.
v$ is a synonym.
Say if you want to grant a select on v$views for a non-dba user
or a dba user explicitly (to call inside a pl.sql)
you need to grant the privelege explicitly.
in this case, you nee to grant a priv on the view.
granting on the synonym will not work.

oracle@mutation#sqlplus "scott/tiger as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Oct 19 08:33:27 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

sys@9i > column object_name format a30
sys@9i >  select owner,object_name,object_type from dba_objects where object_name in ('V$LOG','V_$LOG');

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
SYS                            V_$LOG                         VIEW
PUBLIC                         V$LOG                          SYNONYM


sys@9i > grant select on v$log to test;
grant select on v$log to test
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


sys@9i > grant select on v_$log to test;

Grant succeeded.

Report message to a moderator