v$ views

[Sonia]

Hi,
Information present in the v$views are basically for the life of the instance.
Does these vies have an underlying table which are present in the data dictionary or on what tables are these views based and where are those tables present.

regards
sonia

[SANJAY_G]

The base tables are created by sql.bsq during database creation.

Sanjay

[Edited by SANJAY_G on 07-02-2002 at 11:51 PM]

[Sonia]

thanx sanjay

Is it possible to see which view is based on what table ?

Once we shutdown the d/b does the data stored in these tables will be deleted. Which process deletes the data ?

[SANJAY_G]

You can go through catalog.sql to see the view definitions. the views are created as v_$ and v$ are synonyms.

I am not sure about how (by which process) the tables are cleared or populated.

Sanjay

[Shestakov]

V$table are fixed HARD CODED structures of memory.
U can check this very simple:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 218918824 bytes
Fixed Size 282536 bytes
Variable Size 176160768 bytes
Database Buffers 41943040 bytes
Redo Buffers 532480 bytes
SQL> set lines 120
SQL> set pages 9999
SQL> desc v$instance
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
INSTANCE_NUMBER NUMBER
INSTANCE_NAME VARCHAR2(16)
HOST_NAME VARCHAR2(64)
VERSION VARCHAR2(17)
STARTUP_TIME DATE
STATUS VARCHAR2(7)
PARALLEL VARCHAR2(3)
THREAD# NUMBER
ARCHIVER VARCHAR2(7)
LOG_SWITCH_WAIT VARCHAR2(11)
LOGINS VARCHAR2(10)
SHUTDOWN_PENDING VARCHAR2(3)
DATABASE_STATUS VARCHAR2(17)
INSTANCE_ROLE VARCHAR2(18)
ACTIVE_STATE VARCHAR2(9)

SQL> select sid, program from v$session;

SID PROGRAM
---------- ----------------------------------------
1 ORACLE.EXE
2 ORACLE.EXE
3 ORACLE.EXE
4 ORACLE.EXE
5 ORACLE.EXE
6 ORACLE.EXE
7 sqlplus.exe

7 rows selected.

SQL> select * from v$sysstat where rownum < 20;

STATISTIC# NAME
CLASS VALUE
------------------------------------------------ ---------- ----------
0 logons cumulative 1 7
1 logons current 1 7
2 opened cursors cumulative 1 9
3 opened cursors current 1 1
4 user commits 1 0
5 user rollbacks 1 0
6 user calls 1 64

In this point (when started up only instance we may not have
data dictionary) for example when we going to install new db.
bat all v$tables exists!
-----------------------------------------------------------
AND SQL.BSQ didn't execute before.
-----------------------------------------------------------

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

[pando]

look v$fixed_view_definition
v$ are not based on any ohysical table, they are based on x$ which are memory structure

[SANJAY_G]

Hi Shestakov and Pando,

But does it apply to all the v$ views? My understanding is some of them are available after startup nomount (from memory), some are available after mount (from memory and controlfile) and some are only available only after database is open (data dictionary)

Sanjay

[asharsidd]

Originally posted by Sonia
Hi,
Information present in the v$views are basically for the life of the instance.
Does these vies have an underlying table which are present in the data dictionary or on what tables are these views based and where are those tables present.

regards
sonia

Hi sonia!

THe v$ views are dynamic performance viewsactually public synonyms of V_$ views which are based on X$ tables. To view the dynamic performance views you may query v$FIXED_TABLE.

Oracle server is responsible for populating and cleaning of these tables. Don't really know the process., May be it's SMON.

[jmodic]

Originally posted by SANJAY_G
But does it apply to all the v$ views? My understanding is some of them are available after startup nomount (from memory), some are available after mount (from memory and controlfile) and some are only available only after database is open (data dictionary)

That's because fixed tables (X$ tables) on which fixed views are dependant, are populated at different stages of database startup. Sume are populeted in nomount stage, otehrs when you mount the database, and the rest when database is open. But AFAIK no fixed view is dependent on any data dictionary table.