Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Oracle Database V$ Views

  1. #1
    Administrator
    Join Date
    Oct 2011
    Posts
    13

    Oracle Database V$ Views

    v$archive_dest
    Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest
    These values correspond to the init parameterlog_archive_dest_n.

    v$archive_dest_status
    This view allows to find status and errors for each of the defined
    v$archived_log
    Displays successfully archived redo logs.
    shows received logs on a primary standby database.

    v$archive_gap
    Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).

    v$archive_processes
    This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.

    v$controlfile
    Displays the location and status of each controlfile in thedatabase.

    v$controlfile_record_section
    See sections in a controlfile.

    v$bh
    This dynamic view has an entry for each block in the database buffer cache.
    The column status can be:
    • free
    This block is not in use
    • xcur
    Block held exclusively by this instance
    • scur
    Block held in cache, shared with other instance
    • cr
    Block for consistent read
    • read
    Block being read from disk
    • mrec
    Block in media recovery mode
    • irec
    Block in instance (crash) recovery mode

    v$buffer_pool
    See buffer pools.
    This view's column id can be joined with x$kcbwds.indx
    See also x$kcbwbpd

    v$buffer_pool_statistics
    v$database
    This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
    ADPDB>select log_mode from v$database;

    LOG_MODE
    ------------
    ARCHIVELOG
    checkpoint_change# records the SCN of the last checkpoint.
    switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases. Can be:
    • NOT ALLOWED,
    • SESSIONS ACTIVE,
    • SWITCHOVER PENDING,
    • SWITCHOVER LATENT,
    • TO PRIMARY,
    • TO STANDBY or
    • RECOVERY NEEDED.
    See protection modes in data guard for the columnsprotection_mode and protection_level.
    database_role determines if a database is a primary or alogical standby database or a physical standby database.
    force_logging tells if a database is in force logging mode or not.

    v$datafile
    This view contains an entry for each datafile of the database.
    This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile

    v$datafile_header
    Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint was:
    select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header

    v$dataguard_status
    Shows error messages in a data guard environment.

    v$db_object_cache
    This view displays objects that are cached (pinned) in thelibrary cache. See also dbms_shared_pool.

    v$enqueue_stat
    If there are a lot of enqueue waits "in" v$session_event orv$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets,waits, failures and the cumulative sum of waited time can be found.
    For a list of enqueue types, refer to enqueue types in x$ksqst.
    The column cum_wait_time stems from x$ksqst.ksqstwtim.

    v$eventmetric
    This view is new in Oracle 10g and allows improved timing and statistics.

    v$event_name
    Contains a record for each wait event.

    v$filemetric
    This view is new in Oracle 10g and allows improved timing and statistics.

    v$filestat

    v$fixed_table
    This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables:
    ORA81> select count(*) from v where name like 'V$%';

    COUNT(*)
    ----------
    185
    If you want to know, which x$ tables there are, do a select name from v$fixed_table where name like 'X$%';

    v$fixed_view_definition
    Contains the defintion in its attribute view_definition for the views of v$fixed_table.
    v$flash_recovery_area_usage
    See also v$recovery_file_dest

    v$instance
    instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment.
    dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance.

    v$instance_recovery
    Can, for example, be used to determine the optimal size of redo logs.

    v$latch
    Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleepsindicates how many times a willing to wait process slept.Waiters_woken tells how often a sleeping process was 'disturbed'.

    v$librarycache

    v$lock
    This view stores all information relating to locks in the database. The interesting columns in this view are sid(identifying the session holding or aquiring the lock), type, and the lmode/request pair.
    Important possible values of type are TM (DML or Table Lock),TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction).
    Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1. The possible values forlmode and request are:
    • 1: null,
    • 2: Row Share (SS),
    • 3: Row Exclusive (SX),
    • 4: Share (S),
    • 5: Share Row Exclusive (SSX) and
    • 6: Exclusive(X)
    If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1
    A lock type of JI indicates that a materialized view is being refreshed.
    A more detailed example can be found here
    See also x$kgllk.

    v$locked_object
    Who is locking what:
    select
    oracle_username
    os_user_name,
    locked_mode,
    object_name,
    object_type
    from
    v$locked_object a,dba_objects b
    where
    a.object_id = b.object_id

    v$log
    Contains information on each log group. See also online redo log.
    Comman values for the status column are:
    • UNUSED:
    Oracle8 has never written to this group,
    • CURRENT:
    This is the active group.
    • ACTIVE:
    Oracle has written to this log before, it is needed for instance recovery.
    The active log is the one with the current log sequence number
    • INACTIVE:
    Oracle has written to this log before; it is not needed for instance recovery.

    v$logfile
    This view can be queried to find the filenames, group numbersand states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2

    v$logmnr_contents
    See dbms_logmnr.

    v$log_history
    This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry???
    On physical standby databases, this view shows applied logs.

    v$logstdby
    Can be used to verify that archived redo logs are being applied to standby databases.

    v$managed_standby
    Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service.
    see here
    select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby;
    client_process: the corresponding primary database process. If lgwr log transmission is chosen, one row should have client_process=LGWR. If ARCH transmission is chosen, one row should have ARCH.

    v$mystat
    This view records statistical data about the session that accesses it. Join statistic# with v$statname.

    v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
    See also recording statistics with oracle.

    v$nls_parameters
    The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values.
    v$nls_valid_values
    This view can be used to obtain valid values for NLS parameters such as
    • supported character sets
    • languages
    • territories
    • sorting orders

    v$object_usage
    v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage.
    See On verifying if an index is used.
    v$open_cursor

    v$option
    This view lets you see which options are installed in the server.
    See also dba_registry.

    v$parameter
    Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is:
    select value from v$parameter where name = 'db_block_size'
    The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using alter session or at system level using alter system. A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A parameter is modifiable at system level if issys_modifiable = 'DEFERRED' or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at system level if issys_modifiable = 'DEFERRED' it only affects sessions that are started after chaning the parameter. Additionally, thealter system set ... deferred option must be used.
    There are also some undocumented (or hidden?) parameters.

    v$pgastat
    See also pga.
    Thanks to Oleg who notified me of a typo (v$pgastat instead of v$pga_stat).

    v$process
    Join v$process's addr with v$session paddr.
    The column traceid is equal to the value used in alter session set .

    v$pwfile_users
    Lists all users who have been granted sysdba or sysoperprivileges. See adding user to a password file.

    v$recover_file
    Useful to find out which datafiles need recovery.
    Join with v$datafile to see filenames instead of numbers....

    v$recovery_file_dest
    See also v$flash_recovery_area_usage

    v$reserved_words
    This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user).
    Until 10g, the view only consist of two columns: keyword andlength. From 10gR2 onwards, it has also the columnsreserved, res_type, res_attr, res_semi and duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no)
    See also reserved words in SQL and reserved words in PL/SQL.
    v$resource_limit

    v$rollname
    The names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field.
    v$transaction can be used to track undo by session.

    v$rollstat
    Statistics for rollback segements

    v$session
    The column audsid can be joined withsys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used.
    The fields module and action of v$session can be set withdbms_application_info.set_module. (See v$session_longopsfor an example.
    The field client_info can be set withdbms_application_info.set_client_info
    Join sid with v$sesstat if you want to get some statistical information for a particular sesssion.
    A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).
    A client can set some information in client_info. For example,RMAN related sessions can be found with
    .... where client_info like 'rman%';
    What a session is waiting for can be queried withv$session_wait. However, with Oracle 10g, this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well.
    See also sessions.

    v$sessmetric
    This view is new in Oracle 10g and allows improved timing and statistics.

    v$session_event
    This views is similar to v$system_event. However, it breaks it down to currently connected sessions.
    v$session_event has also the column max_wait that shows the maximum time waited for a wait event.

    v$session_longops
    Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded.
    If the following Procedure is run, it will report its progress in v$session_longops. The Procedure will also set the moduleattribute in v$session which makes it possible to find the sidand serial# of the session.
    create table f(g number);

    create or replace procedure long_proc as
    rindex pls_integer := dbms_application_info.set_session_longops_nohint;
    slno pls_integer;
    -- Name of task
    op_name varchar2(64) := 'long_proc';

    target pls_integer := 0; -- ie. The object being worked on
    context pls_integer; -- Any info
    sofar number; -- how far proceeded
    totalwork number := 1000000; -- finished when sofar=totalwork

    -- desc of target
    target_desc varchar2(32) := 'A long running procedure';

    units varchar2(32) := 'inserts'; -- unit of sofar and totalwork
    begin

    dbms_application_info.set_module('long_proc',null) ;

    dbms_application_info.set_session_longops (
    rindex,
    slno);

    for sofar in 0..totalwork loop

    insert into f values (sofar);

    if mod(sofar,1000) = 0 then
    dbms_application_info.set_session_longops (
    rindex,
    slno,
    op_name,
    target,
    context,
    sofar,
    totalwork,
    target_desc,
    units);

    end if;

    end loop;
    end long_proc;
    If the procedure long_proc is run, you can issue the following query to get feedback on its progress:
    select time_remaining,sofar,elapsed_seconds
    from v$session_longops l, v$session s
    where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc'

    v$session_wait
    This views shows what wait event each session is waiting for, or what the last event was that it waited for.
    In contrast, v$session_event lists the cumulative history of events waited for in a session.
    The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well.
    Since 10g, Oracle displays the v$session_wait information also in the v$session view.

    v$session_wait_history
    This view is new in Oracle 10g and allows improved timing and statistics.

    v$sesstat
    This view is similar to v$mystat except that it shows cumulated statistics for all sessions.
    Join sid with v$session and join statistic# with v$statname.

    v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.

    v$sga
    Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sgain sql plus with the exeption that the latter also show the total.

    v$sgastat
    Showing free space in the sga:
    select * from v$sgastat where name = 'free memory'
    v$sga_dynamic_components
    Information about SGA resize operations since startup.
    This view can also be used to find out the granule size of SGA components.

    v$sga_resize_ops

    v$sort_usage
    See temporary tablespaces

    v$sort_segment
    See Temporary Tablespaces

    v$spparameter
    Returns the values for the spfile.

    v$sql
    v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here.

    v$sqlarea
    Join v$sqlarea's address with v$session's sql_address.
    Find the SQL-text of currently running SQL statements:
    select sql_text from v$sqlarea where users_executing > 0;
    The field version_count indicates how many versions an sql statement has.
    v$sqltext

    v$sql_plan
    variable addr varchar2(20)
    variable hash number
    variable child number

    exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;

    select lpad(' ', 2*(level-1))||operation||' '||
    decode(id, 0, 'Cost = '||position) "OPERATION",
    options, object_name
    from v$sql_plan
    start with (address = :addr
    and hash_value = :hash
    and child_number = :child
    and id=0 )
    connect by prior id = parent_id
    and prior address = address
    and prior hash_value = hash_value
    and prior child_number = child_number
    order by id, position ;
    In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used:
    select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
    v$sqltext_with_newlines
    This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that:
    set serveroutput on size 1000000

    declare
    v_stmt varchar2(16000);
    v_sql_text v$sqltext_with_newlines.sql_text%type;
    v_sid v$session.sid%type;
    begin
    for r in (
    select
    sql_text,s.sid
    from
    v$sqltext_with_newlines t,
    v$session s
    where
    s.sql_address=t.address
    order by s.sid, piece) loop

    v_sid := nvl(v_sid,r.sid);

    if v_sid <> r.sid then
    dbms_output.put_line(v_sid);
    <a href='oru_10028.html'>put_line</a>(v_stmt,100);
    v_sid := r.sid;
    v_stmt := r.sql_text;
    else
    v_stmt := v_stmt || r.sql_text;
    end if;

    end loop;
    dbms_output.put_line(v_sid);
    dbms_output.put_line(v_stmt,100);
    end;

    v$sql_bind_data
    Join cursor_num with cno of v$sql_cursor.

    v$sql_bind_capture
    New with Oracle 10g
    This view captures bind variables for all sessions and is faster than setting 10046 on level 4.

    v$sql_cursor
    Join parent_handle with address of v$sql or v$sqlarea.

    v$sql_workarea
    v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number.

    v$standby_log

    v$statname
    Use this view to get decoded names for the statistic# field ofv$mystat, v$sysstat and

    v$sesstat.

    v$sysaux_occupants

    v$sysaux_occupants doesn't exist in Oracle versions prior toOracle 10g.
    See occupants in the sysaux tablepsaces.

    v$sysmetric
    This view is new in Oracle 10g and allows improved timing and statistics.

    v$sysmetric_history
    This view is new in Oracle 10g and allows improved timing and statistics.

    v$sysstat
    v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database.
    For example, it is possible to find out the CPU time (name = 'CPU used by this session')
    This view is (among others) used to calculate the Hit Ratio.

    v$system_event
    This view displays the count (total_waits) of all wait eventssince startup of the instance.
    If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited.
    The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second.
    total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat.
    v$enqueue_stat can be used to break down waits on theenqueue wait event.
    While this view totals all events in an instance,v$session_event breaks it down to all currently connectedsessions.
    v$undostat
    undo tablespaces
    v$tempfile
    v$tempseg_usage
    v$tempseg_usage is a public synonym for v$sort_usage.
    v$tempstat
    v$thread
    The Oracle SID can be retrieved through select instance from v$thread
    v$timer
    This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0.

    v$transaction
    Important fields of v$transaction are used_ublk andused_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with theusn field of v$rollname. This is demonstrated in
    Transactions generate undo

    v$timezone_names
    See also timezones for some values of tzabbrev.
    v$transportable_platform
    Which platforms are supported for cross platform transportable tablespaces.

    v$version
    Use this view to find out what version you actually work on:select * from v$version;
    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    PL/SQL Release 8.1.7.0.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production

    v$waitstat
    total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
    Last edited by Hemant; 01-03-2012 at 01:51 AM.

+ Reply to Thread

Similar Threads

  1. Materialized Views,Complete and Fast Refresh in 11g Oracle Database
    By ajaychandi in forum Database Performance Management,Database Links,Materialized Views
    Replies: 0
    Last Post: 01-12-2012, 11:03 PM
  2. Oracle Database V$ Views
    By Pradeep in forum Oracle Database Administration 10g and 11g
    Replies: 0
    Last Post: 12-14-2011, 05:04 AM
  3. Oracle ASM Views and Usage - Quick Reference/Understanding
    By Hemant in forum RAC Installation, ASM Install , ASM Administration
    Replies: 0
    Last Post: 11-21-2011, 04:17 PM

Tags for this Thread

Posting Permissions

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

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics