v$ views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: v$ views

  1. #1
    Join Date
    Feb 2002
    Posts
    267
    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

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    The base tables are created by sql.bsq during database creation.

    Sanjay

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

  3. #3
    Join Date
    Feb 2002
    Posts
    267
    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 ?

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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


  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    look v$fixed_view_definition
    v$ are not based on any ohysical table, they are based on x$ which are memory structure

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  8. #8
    Join Date
    Jun 2002
    Posts
    73

    Wink

    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.
    You cannot succeed if you fear to face challenges.
    MAS
    BE(CS) , OCP 8/8i.



  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width