Home » RDBMS Server » Performance Tuning » V_$view VS v$views
V_$view VS v$views [message #171266] Tue, 09 May 2006 04:16 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member
Hi,
I am using Oracle 9i and want to use runstats of Thomas Kyte.
However i have access to v$views and not on v_$views which are required for running runstats.

Can anybody suggest me what is difference between v_$views and v$views?

Thanks and Regards,
Pratap
Re: V_$view VS v$views [message #171274 is a reply to message #171266] Tue, 09 May 2006 04:40 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
v_$ views are the actual views, v$views are public synonyms of those views. query the dba_objects tables to see this.

Re: V_$view VS v$views [message #171276 is a reply to message #171266] Tue, 09 May 2006 04:42 Go to previous messageGo to next message
Frank Naude
Messages: 4580
Registered: April 1998
Senior Member
You need to run ?/rdbms/admin/catalog.sql. It contains statements like:

create or replace view v_$instance as select * from v$instance;


Re: V_$view VS v$views [message #171279 is a reply to message #171276] Tue, 09 May 2006 04:48 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member
Hi,
Thanks for your quick reply.

Also i am facing one problem here,
i am able to select from v$view but unable to create view on that as described below
Please suggest.

DEV13>select * from v$mystat where rownum<2;

SID STATISTIC# VALUE
---------- ---------- ----------
43 0 1

DEV13>create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time', hsecs from v$timer;
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges

Thanks in Advance
Pratap
Re: V_$view VS v$views [message #171283 is a reply to message #171274] Tue, 09 May 2006 05:02 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member
Hi,
Thanks for your quick reply.

Does it mean that even for Autotrace i can use v$statename,v$session and v$systat rather than corresponding v_$views (which plustrce.sql does)?

Thanks and Regards,
Pratap
Re: V_$view VS v$views [message #171306 is a reply to message #171276] Tue, 09 May 2006 06:30 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member
Hi Frank,
please help me,

i am facing one problem here,
i am able to select from v$view but unable to create view on that as described below
Please suggest.

DEV13>select * from v$mystat where rownum<2;

SID STATISTIC# VALUE
---------- ---------- ----------
43 0 1

DEV13>create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time', hsecs from v$timer;
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges

Thanks in Advance
Pratap
Re: V_$view VS v$views [message #171319 is a reply to message #171306] Tue, 09 May 2006 07:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Inside PL/SQL you need a direct access to all views/tables.
In case of v$views, you need access to corresponding v_$views
SQL> get a
  1  create or replace view stats
  2   as select 'STAT...' || a.name name, b.value
  3   from v$statname a, v$mystat b
  4   where a.statistic# = b.statistic#
  5   union all
  6   select 'LATCH.' || name, gets
  7   from v$latch
  8   union all
  9*  select 'STAT...Elapsed Time', hsecs from v$timer
SQL> @a
 from v$statname a, v$mystat b
                    *
ERROR at line 3:
ORA-01031: insufficient privileges


SQL> !sqlplus -s "sys/sys as sysdba"
grant select on v_$statname to scott;

Grant succeeded.

grant select on v_$mystat to scott;

Grant succeeded.

grant select on v_$latch to scott;

Grant succeeded.

grant select on v_$timer to scott;

Grant succeeded.

exit;

SQL> @a

View created.
Re: V_$view VS v$views [message #171326 is a reply to message #171319] Tue, 09 May 2006 07:20 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member
Hi Mahesh,
great help.

i must stop counting saying thanks to you. Smile
By the way what happened to your vacation? as you were referring yesteday that you are going on vacation.

Best Regards,
Pratap
Re: V_$view VS v$views [message #171332 is a reply to message #171326] Tue, 09 May 2006 07:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Vacationing in my yard Laughing
Still i am. Will be in and out.
Regards~

[Updated on: Tue, 09 May 2006 07:47]

Report message to a moderator

Re: V_$view VS v$views [message #171367 is a reply to message #171332] Tue, 09 May 2006 09:50 Go to previous message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member
Hi mahesh,
Thats great!!! Laughing
its' really news for me that even people like you go for such vacation.

Best Regards,
Pratap
Previous Topic: pinning objects ?? but when ??
Next Topic: regarding Large Pool usage ??
Goto Forum:
  


Current Time: Tue May 28 09:19:22 CDT 2024