Oracle8 Server Reference
Release 8.0

A54645_01

Library

Product

Contents

Index

Prev Next

3
Dynamic Performance (V$) Views

This chapter describes the dynamic performance views, which are also known as V$ views.

The following topics are included in this chapter:

Dynamic Performance Views

The Oracle Server contains a set of underlying views that are maintained by the server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.

Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. These views can be selected from, but never updated or altered by the user.

The file CATALOG.SQL contains definitions of the views and public synonyms for the dynamic performance views. You must run CATALOG.SQL to create these views and synonyms.

V$ Views

Dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. Database administrators or users should only access the V$ objects, not the V_$ objects.

The dynamic performance views are used by Enterprise Manager and Oracle Trace, which is the primary interface for accessing information about system performance.

Suggestion: Once the instance is started, the V$ views that read from memory are accessible. Views that read data from disk require that the database be mounted.

Warning: Information about the dynamic performance views is presented for completeness only; this information does not imply a commitment to support these views in the future.

GV$ Views

In Oracle, there is an additional class of fixed views, the GV$ (Global V$) fixed views. For each of the V$ views described in this chapter (with the exception of V$CACHE_LOCK, V$LOCK_ACTIVITY, V$LOCKS_WITH_COLLISIONS, and V$ROLLNAME), there is a GV$ view. In a parallel server environment, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view possesses an additional column named INST_ID with type integer. The INST_ID column displays the instance number from which the associated V$ view information was obtained. The INST_ID column can be used as a filter to retrieve V$ information from a subset of available instances. For example, the query:

SELECT * FROM GV$LOCK WHERE INST_ID = 2 OR INST_ID = 5 

retrieves the information from the V$ views on instances 2 and 5.

The GV$ views can be used to return information on groups of instances defined with the OPS_ADMIN_GROUP parameter. For more information see "OPS_ADMIN_GROUP" on page 1-83 and Oracle8 Parallel Server Concepts & Administration.

Restrictions on GV$ Views

GV$ views have the following restrictions:

Access to the Dynamic Performance Tables

After installation, only username SYS or anyone with SYSDBA ROLE has access to the dynamic performance tables.

For more information, see Oracle Enterprise Manager: Performance Monitoring User's Guide.

View Descriptions

This section lists the columns and public synonyms for the dynamic performance views.

FILEXT$

FILEXT$ is created the first time you turn on the AUTOEXTEND characteristic for a datafile.

Column   Datatype   Description  

FILE#  

NUMBER  

File identifier  

MAXEXTEND  

NUMBER  

Value from the MAXSIZE parameter  

INC  

NUMBER  

Value from the NEXT parameter  

For more information, see the Oracle8 Server Administrator's Guide.

V$ACCESS

This view displays objects in the database that are currently locked and the sessions that are accessing them.

Column   Datatype   Description  

SID  

NUMBER  

Session number that is accessing an object  

OWNER  

VARCHAR2(64)  

Owner of the object  

OBJECT  

VARCHAR2(1000)  

Name of the object  

TYPE  

VARCHAR2(12)  

Type identifier for the object  

V$ACTIVE_INSTANCES

This view maps instance names to instance numbers for all instances that have the database currently mounted.

Column   Datatype   Description  

INST_NUMBER  

NUMBER  

The instance number  

INST_NAME  

VARCHAR2(60)  

The instance name  

V$ARCHIVE

This view contains information on archive logs for each thread in the database system. Each row provides information for one thread. This information is also available in V$LOG. Oracle recommends that you use V$LOG. For more information, see "V$LOG" on page 3-57.

Column   Datatype   Description  

GROUP#  

NUMBER  

Log file group number  

THREAD#  

NUMBER  

Log file thread number  

SEQUENCE#  

NUMBER  

Log file sequence number  

CURRENT  

VARCHAR2(3)  

Archive log currently in use  

FIRST_CHANGE#  

NUMBER  

First SCN stored in the current log  

V$ARCHIVE_DEST

This view describes, for the current instance, all the archive log destinations, their current value, mode, and status.

Column   Datatype   Description  

ARCMODE  

VARCHAR2(12)  

Archiving mode:

  • MUST SUCCEED: This is a must-succeed destination
  • BEST-EFFORT: This is a best-effort destination
 

STATUS  

VARCHAR2(8)  

Status:

  • NORMAL: This destination is normal
  • DISABLED: This destination has been disabled
 

DESTINATION  

VARCHAR2(256)  

Destination text string  

For more information on archived log destinations, see "LOG_ARCHIVE_DEST" on page 1-58, "LOG_ARCHIVE_DUPLEX_DEST" on page 1-58, and "LOG_ARCHIVE_MIN_SUCCEED_DEST" on page 1-60

V$ARCHIVED_LOG

This view displays archived log information from the controlfile including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy.

Column   Datatype   Description  

RECID  

NUMBER  

Archived log record ID  

STAMP  

NUMBER  

Archived log record stamp  

NAME  

VARCHAR2(512)  

Archived log file name. If set to NULL, the log file was cleared before it was archived  

THREAD#  

NUMBER  

Redo thread number  

SEQUENCE#  

NUMBER  

Redo log sequence number  

RESETLOGS_CHANGE#  

NUMBER  

Resetlogs change# of the database when this log was written  

RESETLOGS_TIME  

DATE  

Resetlogs time of the database when this log was written  

FIRST_CHANGE#  

NUMBER  

First change# in the archived log  

FIRST_TIME  

DATE  

Timestamp of the first change  

NEXT_CHANGE#  

NUMBER  

First change in the next log  

NEXT_TIME  

DATE  

Timestamp of the next change  

BLOCKS  

NUMBER  

Size of the archived log in blocks  

BLOCK_SIZE  

NUMBER  

Redo log block size  

COMPLETION_TIME  

DATE  

Time when the archiving completed  

DELETED  

VARCHAR2(3)  

YES/NO  

V$BACKUP

This view displays the backup status of all online datafiles.

Column   Datatype   Description  

FILE#  

NUMBER  

File identifier  

STATUS  

VARCHAR2(18)  

File status: NOT ACTIVE, ACTIVE (backup in progress), OFFLINE NORMAL, or description of an error  

CHANGE#  

NUMBER  

System change number when backup started  

TIME  

DATE  

Time the backup started  

V$BACKUP_CORRUPTION

This view displays information about corruptions in datafile backups from the controlfile. Note that corruptions are not tolerated in the controlfile and archived log backups.

Column   Datatype   Description  

RECID  

NUMBER  

Backup corruption record ID  

STAMP  

NUMBER  

Backup corruption record stamp  

SET_STAMP  

NUMBER  

Backup set stamp  

SET_COUNT  

NUMBER  

Backup set count  

PIECE#  

NUMBER  

Backup piece number  

FILE#  

NUMBER  

Datafile number  

BLOCK#  

NUMBER  

First block of the corrupted range  

BLOCKS  

NUMBER  

Number of contiguous blocks in the corrupted range  

CORRUPTION
_CHANGE#  

NUMBER  

Change# at which the logical corruption was detected. Set to 0 to indicate media corruption  

MARKED_CORRUPT  

VARCHAR2(3)  

YES/NO. If set to YES the blocks were not marked corrupted in the datafile, but were detected and marked as corrupted while making the datafile backup  

V$BACKUP_DATAFILE

This view displays backup datafile and backup controlfile information from the controlfile.

Column   Datatype   Description  

RECID  

NUMBER  

Backup datafile record ID  

STAMP  

NUMBER  

Backup datafile record stamp  

SET_STAMP  

NUMBER  

Backup set stamp  

SET_COUNT  

NUMBER  

Backup set count  

FILE#  

NUMBER  

Datafile number. Set to 0 for controlfile  

CREATION_CHANGE#  

NUMBER  

Creation change of the datafile  

CREATION_TIME  

DATE  

Creation timestamp of the datafile  

RESETLOGS_CHANGE#  

NUMBER  

Resetlogs change# of the datafile when it was backed up  

RESETLOGS_TIME  

DATE  

Resetlogs timestamp of the datafile when it was backed up  

INCREMENTAL_LEVEL  

NUMBER  

(0-4) incremental backup level  

INCREMENTAL_CHANGE#  

NUMBER  

All blocks changed after incremental change# is included in this backup. Set to 0 for a full backup  

CHECKPOINT_CHANGE#  

NUMBER  

All changes up to checkpoint change# are included in this backup  

CHECKPOINT_TIME  

DATE  

Timestamp of the checkpoint  

ABSOLUTE_FUZZY
_CHANGE#  

NUMBER  

Highest change# in this backup  

MARKED_CORRUPT  

NUMBER  

Number of blocks marked corrupt  

MEDIA_CORRUPT  

NUMBER  

Number of blocks media corrupt  

LOGICALLY_CORRUPT  

NUMBER  

Number of blocks logically corrupt  

DATAFILE_BLOCKS  

NUMBER  

Size of the datafile in blocks at backup time. This value is also the number of blocks taken by the datafile restarted from this backup  

BLOCKS  

NUMBER  

Size of the backup datafile in blocks. Unused blocks are not copied to the backup  

BLOCK_SIZE  

NUMBER  

Block size  

OLDEST_OFFLINE
_RANGE  

NUMBER  

The RECID of the oldest offline range record in this backup controlfile. 0 for datafile backups  

COMPLETION_TIME  

DATE  

The time completed.  

V$BACKUP_DEVICE

This view displays information about supported backup devices. If a device type does not support named devices, then one row with the device type and a null device name is returned for that device type. If a device type supports named devices then one row is returned for each available device of that type. The special device type DISK is not returned by this view because it is always available.

Column   Datatype   Description  

DEVICE_TYPE  

VARCHAR2(17)  

Type of the backup device  

DEVICE_NAME  

VARCHAR2(512)  

Name of the backup device  

V$BACKUP_PIECE

This view displays information about backup pieces from the controlfile. Each backup set consist of one or more backup pieces.

Column   Datatype   Description  

RECID  

NUMBER  

Backup piece record ID  

STAMP  

NUMBER  

Backup piece record stamp  

SET_STAMP  

NUMBER  

Backup set stamp  

SET_COUNT  

NUMBER  

Backup set count  

PIECE#  

NUMBER  

Backup piece number (1-N)  

DEVICE_TYPE  

VARCHAR2(17)  

Type of the device on which the backup piece resides. Set to DISK for backup sets on disk. See V$BACKUP_DEVICE  

HANDLE  

VARCHAR2(513)  

Backup piece handle identifies the backup piece on restore  

COMMENTS  

VARCHAR2(81)  

Comment returned by the operating system or storage subsystem. Set to NULL for backup pieces on disk. This value is informational only; not needed for restore.  

MEDIA  

VARCHAR2(65)  

Name of the media on which the backup piece resides. This value is informational only; not needed for restore.  

CONCUR  

VARCHAR2(3)  

YES/NO, Indicates whether the piece on a media that can be accessed concurrently  

TAG  

VARCHAR2(32)  

Backup piece tag. The tag is specified at backup set level, but stored at piece level  

DELETED  

VARCHAR2(3)  

If set to YES indicates the piece is deleted, otherwise set to NO  

START_TIME  

DATE  

The starting time.  

COMPLETION_TIME  

DATE  

The completion time.  

ELAPSED_SECONDS  

NUMBER  

The number of elapsed seconds.  

V$BACKUP_REDOLOG

This view displays information about archived logs in backup sets from the controlfile. Note that online redo logs cannot be backed up directly; they must be archived first to disk and then backed up. An archive log backup set can contain one or more archived logs.

Column   Datatype   Description  

RECID  

NUMBER  

Record ID for this row. It is an integer that identifies this row.  

STAMP  

NUMBER  

Timestamp used with RECID to uniquely identify this row  

SET_STAMP  

NUMBER  

One of the foreign keys for the row of the V$BACKUP_SET table that identifies this backup set  

SET_COUNT  

NUMBER  

One of the foreign keys for the row of the V$BACKUP_SET table that identifies this backup set  

THREAD#  

NUMBER  

Thread number for the log  

SEQUENCE#  

NUMBER  

Log sequence number  

RESETLOGS_CHANGE#  

NUMBER  

Change number of the last resetlogs before the log was written  

RESETLOGS_TIME  

DATE  

Change time of the last resetlogs before the log was written. These will be the same for all logs in a backup set  

FIRST_CHANGE#  

NUMBER  

SCN when the log was switched into. The redo in the log is at this SCN and greater  

FIRST_TIME  

DATE  

Time allocated when the log was switched into  

NEXT_CHANGE#  

NUMBER  

SCN when the log was switched out of. The redo in the log is below this SCN  

NEXT_TIME  

DATE  

Time allocated when the log was switched out of  

BLOCKS  

NUMBER  

Size of the log in logical blocks including the header block  

BLOCK_SIZE  

NUMBER  

Size of the log blocks in bytes  

V$BACKUP_SET

This view displays backup set information from the controlfile. A backup set record is inserted after the backup set is successfully completed.

Column   Datatype   Description  

RECID  

NUMBER  

Backup set record ID  

STAMP  

NUMBER  

Backup set record timestamp  

SET_STAMP  

NUMBER  

Backup set stamp. The backup set stamp and count uniquely identify the backup set.

Primary key for the V$BACKUP_SET table, and the foreign key for the following tables:
V$BACKUP_PIECE
V$BACKUP_DATAFILE
V$BACKUP_REDOLOG
V$BACKUP_CORRUPTION  

SET_COUNT  

NUMBER  

Backup set count. The backup set count is incremented by one every time a new backup set is started (if the backup set is never completed the number is "lost"). If the controlfile is recreated then the count is reset to 1. Therefore the count must be used with the stamp to uniquely identify a backup set.

Primary key for the V$BACKUP_SET table, and the foreign key for the following tables:
V$BACKUP_PIECE
V$BACKUP_DATAFILE
V$BACKUP_REDOLOG
V$BACKUP_CORRUPTION  

BACKUP_TYPE  

VARCHAR2(1)  

Type of files that are in this backup. If the backup contains archived redo logs, the value is \QL'. If this is a datafile full backup, the value is \QD'. If this is an incremental backup, the value is \QI'.  

CONTROLFILE
_INCLUDED  

VARCHAR2(3)  

Set to YES if there is a controlfile included in this backup set, otherwise set to NO.  

INCREMENTAL
_LEVEL  

NUMBER  

Location where this backup set fits into the database's backup strategy. Set to zero for full datafile backups, non-zero for incremental datafile backups, and NULL for archivelog backups.  

PIECES  

NUMBER  

Number of distinct backup pieces in the backup set  

COMPLETION_TIME  

DATE  

When the backup completes successfully, this is set to the completion time. This is the same time that was returned by backupEnd. If the backup is still in progress or has failed, this is set to NULL.  

ELAPSED_SECONDS  

NUMBER  

The number of elapsed seconds.  

BLOCK_SIZE  

NUMBER  

Block size of the backup set  

V$BGPROCESS

This view describes the background processes.

Column   Datatype   Description  

PADDR  

RAW(4)  

Address of the process state object  

NAME  

VARCHAR2  

Name of this background process  

DESCRIPTION  

VARCHAR2  

Description of the background process  

ERROR  

NUMBER  

Error encountered  

V$BH

This is a Parallel Server view. This view gives the status and number of pings for every buffer in the SGA.

Column   Datatype   Description  

FILE#  

NUMBER  

Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES)  

BLOCK#  

NUMBER  

Block number  

STATUS  

VARCHAR2(1)  

FREE= not currently in use
XCUR= exclusive
SCUR= shared current
CR= consistent read
READ= being read from disk
MREC= in media recovery mode
IREC= in instance recovery mode  

XNC  

NUMBER  

Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete but is retained for historical compatibility.  

LOCK_ELEMENT
_ADDR  

RAW(4)  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

LOCK_ELEMENT
_NAME  

NUMBER  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

LOCK_ELEMENT
_CLASS  

NUMBER  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

FORCED_READS  

NUMBER  

Number of times the block had to be made re-read from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on this block in lock mode.  

FORCED_WRITES  

NUMBER  

Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the PCM lock on the block in conflicting mode.  

DIRTY  

VARCHAR2(1)  

Y = block modified.  

TEMP  

VARCHAR2(1)  

Y = temporary block  

PING  

VARCHAR2(1)  

Y = block pinged  

STALE  

VARCHAR2(1)  

Y = block is stale  

DIRECT  

VARCHAR2(1)  

Y = direct block  

NEW  

VARCHAR2(1)  

Always set to N. This column is obsolete but is retained for historical compatibility  

OBJD  

NUMBER  

Database object number of the block that the buffer represents  

For more information, see Oracle8 Parallel Server Concepts & Administration.

V$BUFFER_POOL

This view displays information about all buffer pools available for the instance. The "sets" pertain to the number of LRU latch sets. For more information, see "DB_BLOCK_LRU_LATCHES" on page 1-28.

Column   Datatype   Description  

INST_ID  

NUMBER  

Instance ID  

ID  

NUMBER  

Buffer pool ID number  

NAME  

VARCHAR2  

Buffer pool name  

LO_SETID  

NUMBER  

Low set ID number  

HI_SETID  

NUMBER  

High set ID number  

SET_COUNT  

NUMBER  

Number of sets in this buffer pool. This is HI_SETID - LO_SETID + 1  

SIZE  

NUMBER  

Number of buffers allocated to the buffer pool  

LO_BNUM  

NUMBER  

Low buffer number for this pool  

HI_BNUM  

NUMBER  

High buffer number for this pool  

V$CACHE

This is a Parallel Server view. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.

Column   Datatype   Description  

FILE#  

NUMBER  

Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES)  

BLOCK#  

NUMBER  

Block number  

STATUS  

VARCHAR2(1)  

Status of block:
FREE = not currently in use
XCUR = exclusive
SCUR = shared current
CR = consistent read
READ = being read from disk
MREC = in media recovery mode
IREC = in instance recovery mode  

XNC  

NUMBER  

Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete but is retained for historical compatibility.  

NAME  

VARCHAR2(30)  

Name of the database object containing the block  

KIND  

VARCHAR2(12)  

Type of database object. See Table 3-1.  

OWNER#  

NUMBER  

Owner number  

LOCK_ELEMENT_ADDR  

RAW(4)  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

LOCK_ELEMENT_
NAME  

NUMBER  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

PARTITION_NAME  

VARCHAR2(30)  

NULL for non-partitioned objects  

For more information, see Oracle8 Parallel Server Concepts & Administration.

Table 3-1: Values for the KIND column
Type Number   KIND Value   Type Number   KIND Value  

1  

INDEX  

11  

PACKAGE BODY  

2  

TABLE  

12  

TRIGGER  

3  

CLUSTER  

13  

TYPE  

4  

VIEW  

14  

TYPE BODY  

5  

SYNONYM  

19  

TABLE PARTITION  

6  

SEQUENCE  

20  

INDEX PARTITION  

7  

PROCEDURE  

21  

LOB  

8  

FUNCTION  

22  

LIBRARY  

9  

PACKAGE  

NULL  

UNKNOWN  

10  

NON-EXISTENT  

------  

-------  

V$CACHE_LOCK

This is a Parallel Server view.

Column   Datatype   Description  

FILE#  

NUMBER  

Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES)  

BLOCK#  

NUMBER  

Block number  

STATUS  

VARCHAR2(4)  

Status of block:
FREE = not currently in use
XCUR = exclusive
SCUR = shared current
CR = consistent read
READ = being read from disk
MREC = in media recovery mode
IREC = in instance recovery mode  

XNC  

NUMBER  

Number of parallel cache management (PCM) lock conversions due to contention with another instance  

NAME  

VARCHAR2(30)  

Name of the database object containing the block  

KIND  

VARCHAR2(12)  

Type of database object. See Table 3-1 on page 3-15.  

OWNER#  

NUMBER  

Owner number  

LOCK_ELEMENT
_ADDR  

RAW(4)  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

LOCK_ELEMENT
_NAME  

NUMBER  

The address of the lock element that contains the PCM lock that is covering the
buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

FORCED_READS  

NUMBER  

Number of times the block had to be made re-read from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on this block in lock mode.  

FORCED_WRITES  

NUMBER  

Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the PCM lock on the block in conflicting mode.  

INDX  

NUMBER  

Platform specific lock manager identifier  

CLASS  

NUMBER  

Platform specific lock manager identifier  

V$CACHE_LOCK is similar to V$CACHE, except for the platform-specific lock manager identifiers. This information may be useful if the platform- specific lock manager provides tools for monitoring the PCM lock operations that are occurring. For example, first query to find the lock element address using INDX and CLASS, then query V$BH to find the buffers that are covered by the lock. See also "V$CACHE" on page 3-14

For more information, see Oracle8 Parallel Server Concepts & Administration.

V$CIRCUIT

This view contains information about virtual circuits, which are user connections to the database through dispatchers and servers.

Column   Datatype   Description  

CIRCUIT  

RAW(4)  

Circuit address  

DISPATCHER  

RAW(4)  

Current dispatcher process address  

SERVER  

RAW(4)  

Current server process address  

WAITER  

RAW(4)  

Address of server process that is waiting for the (currently busy) circuit to become available  

SADDR  

RAW(4)  

Address of session bound to the circuit  

STATUS  

VARCHAR2  

Status of the circuit: BREAK (currently interrupted), EOF (about to be removed), OUTBOUND (an outward link to a remote database), NORMAL (normal circuit into the local database)  

QUEUE  

VARCHAR2  

Queue the circuit is currently on: COMMON (on the common queue, waiting to be picked up by a server process), DISPATCHER (waiting for the dispatcher), SERVER (currently being serviced), OUTBOUND (waiting to establish an outbound connection), NONE (idle circuit)  

MESSAGE0  

NUMBER  

Size in bytes of the messages in the first message buffer  

MESSAGE1  

NUMBER  

Size in bytes of the messages in the second message buffer.  

MESSAGES  

NUMBER  

Total number of messages that have gone through this circuit  

BYTES  

NUMBER  

Total number of bytes that have gone through this circuit  

BREAKS  

NUMBER  

Total number of breaks (interruptions) for this circuit  

V$CLASS_PING

V$CLASS_PING displays the number of blocks pinged per block class. Use this view to compare contentions for blocks in different classes.

Column   Datatype   Description  

CLASS  

NUMBER  

Number that represents the block class  

X_2_NULL  

NUMBER  

Number of lock conversions from Exclusive-to-NULL for all blocks of the specified CLASS  

X_2_NULL_FORCED_WRITE  

NUMBER  

Number of forced writes that occur for blocks of the specified CLASS due to Exclusive-to-NULL conversions  

X_2_NULL_FORCED_STALE  

NUMBER  

Number of times a block in the CLASS was made STALE due to Exclusive-to-NULL conversions  

X_2_S  

NUMBER  

Number of lock conversions from Exclusive-to-Shared for all blocks of the specified CLASS  

X_2_S_FORCED_WRITE  

NUMBER  

Number of forced writes that occur for blocks of the specified CLASS due to Exclusive-to-Shared conversions  

X_2_SSX  

NUMBER  

Number of lock conversions from Exclusive-to-Sub Shared Exclusive for all blocks of the specified CLASS  

X_2_SSX_FORCED_WRITE  

NUMBER  

Number of forced writes that occur for blocks of the specified CLASS due to Exclusive-to-Sub Shared Exclusive conversions  

S_2_NULL  

NUMBER  

Number of lock conversions from Shared-to-NULL for all blocks of the specified CLASS  

S_2_NULL_FORCED_STALE  

NUMBER  

Number of times a block in the CLASS was made STALE due to Shared-to-NULL conversions  

SS_2_NULL  

NUMBER  

Number of lock conversions from Sub Shared-to-NULL for all blocks of the specified CLASS  

NULL_2_X  

NUMBER  

Number of lock conversions from NULL-to-Exclusive for all blocks of the specified CLASS  

S_2_X  

NUMBER  

Number of lock conversions from Shared-to-Exclusive for all blocks of the specified CLASS  

SSX_2_X  

NUMBER  

Number of lock conversions from Sub Shared Exclusive-to-Exclusive for all blocks of the specified CLASS  

NULL_2_S  

NUMBER  

Number of lock conversions from NULL-to-Shared for all blocks of the specified CLASS  

NULL_2_SS  

NUMBER  

Number of lock conversions from NULL-to-Sub Shared for all blocks of the specified CLASS  

V$COMPATIBILITY

This view displays features in use by the database instance that may prevent downgrading to a previous release. This is the dynamic (SGA) version of this information, and may not reflect features that other instances have used, and may include temporary incompatibilities (like UNDO segments) that will not exist after the database is shut down cleanly.

Column   Datatype   Description  

TYPE_ID  

VARCHAR2(8  

Internal feature identifier  

RELEASE  

VARCHAR2(60)  

Release in which that feature appeared  

DESCRIPTION  

VARCHAR2(64)  

Description of the feature  

V$COMPATSEG

This view lists the permanent features in use by the database that will prevent moving back to an earlier release.

Column   Datatype   Description  

TYPE_ID  

VARCHAR2(8)  

Internal feature identifier  

RELEASE  

VARCHAR2(60)  

Release in which that feature appeared. The software must be able to interpret data formats added in that release  

UPDATED  

VARCHAR2(60)  

Release that first used the feature  

V$CONTROLFILE

This view lists the names of the control files.

Column   Datatype   Description  

STATUS  

VARCHAR2(7)  

INVALID if the name cannot be determined, which should not occur. NULL if the name can be determined.  

NAME  

VARCHAR2(257)  

The name of the control file.  

V$CONTROLFILE_RECORD_SECTION

This view displays information about the controlfile record sections.

Column   Datatype   Description  

TYPE  

VARCHAR2(17)  

DATABASE/CKPT PROGRESS/REDO THREAD/REDO LOG/DATAFILE/FILENAME/TABLESPACE/LOG HISTORY/OFFLINE RANGE/ARCHIVED LOG/BACKUP SET/BACKUP PIECE/BACKUP DATAFILE/BACKUP REDOLOG/DATAFILE COPY/BACKUP CORRUPTION/COPY CORRUPTION/DELETED OBJECT  

RECORD_SIZE  

NUMBER  

Record size in bytes  

RECORDS_TOTAL  

NUMBER  

Number of records allocated for the section  

RECORDS_USED  

NUMBER  

Number of records used in the section  

FIRST_INDEX  

NUMBER  

Index (position) of the first record  

LAST_INDEX  

NUMBER  

Index of the last record  

LAST_RECID  

NUMBER  

Record ID of the last record  

V$COPY_CORRUPTION

This view displays information about datafile copy corruptions from the controlfile.

Column   Datatype   Description  

RECID  

NUMBER  

Copy corruption record ID  

STAMP  

NUMBER  

Copy corruption record stamp  

COPY_RECID  

NUMBER  

Datafile copy record ID  

COPY_STAMP  

NUMBER  

Datafile copy record stamp  

FILE#  

NUMBER  

Datafile number  

BLOCK#  

NUMBER  

First block of the corrupted range  

BLOCKS  

NUMBER  

Number of contiguous blocks in the corrupted range  

CORRUPTION_CHANGE#  

NUMBER  

Change# at which the logical corruption was detected. Set to 0 to indicate media corruption  

MARKED_CORRUPT  

VARCHAR2(3)  

YES/NO. If set to YES the blocks were not marked corrupted in the datafile, but were detected and marked as corrupted while making the datafile copy  

V$DATABASE

This view contains database information from the control file.

Column   Datatype   Description  

NAME  

VARCHAR2  

Name of the database  

CREATED  

DATE  

Creation date  

LOG_MODE  

VARCHAR2  

Archive log mode: NOARCHIVELOG or ARCHIVELOG  

CHECKPOINT_ CHANGE#  

NUMBER  

Last SCN checkpointed  

ARCHIVE_CHANGE#  

NUMBER  

Last SCN archived  

DBID  

NUMBER  

Database ID calculated when database is created and stored in all file headers  

RESETLOGS_CHANGE#  

NUMBER  

Change# at open resetlogs  

RESETLOGS_TIME  

DATE  

Timestamp of open resetlogs  

CONTROLFILE_TYPE  

VARCHAR2(7)  

CURRENT/STANDBY/CLONE/BACKUP/CREATED. STANDBY indicates database is in standby mode. CLONE indicates a clone database. BACKUP/CREATED indicates database is being recovered using a backup or created controlfile. A standby database activate or database open after recovery changes the type to CURRENT  

CONTROLFILE_CREATED  

DATE  

Controlfile creation timestamp  

CONTROLFILE_SEQUENCE#  

NUMBER  

Controlfile sequence number incremented by controlfile transactions  

CONTROLFILE_CHANGE#  

NUMBER  

Last change# in backup controlfile. Set to NULL if the controlfile is not a backup  

CONTROLFILE_TIME  

DATE  

Last timestamp in backup controlfile. Set to NULL if the controlfile is not a backup  

OPEN_RESETLOGS  

VARCHAR2(11)  

NOT ALLOWED/ALLOWED/REQUIRED. Indicates whether next database open allows or requires the resetlogs option  

V$DATAFILE

This view contains datafile information from the control file. See also the "V$DATAFILE_HEADER" on page 3-26 view which displays information from datafile headers.

Column   Datatype   Description  

FILE#  

NUMBER  

File identification number  

STATUS  

VARCHAR2  

Type of file (system or user) and its status. Values: OFFLINE, ONLINE, SYSTEM, RECOVER, SYSOFF (an offline file from the SYSTEM tablespace).  

ENABLED  

VARCHAR2(10)  

Describes how accessible the file is from SQL. It is one of the values in Table 3-2 on page 3-24.  

CHECKPOINT_ CHANGE#  

NUMBER  

SCN at last checkpoint  

CHECKPOINT_TIME  

DATE  

Time stamp of the checkpoint#  

UNRECOVERABLE_ CHANGE#  

NUMBER  

Last unrecoverable change# made to this datafile. This column is always updated when an unrecoverable operation completes.  

UNRECOVERABLE_
TIME  

DATE  

Time stamp of the last unrecoverable change  

BYTES  

NUMBER  

Current size in bytes; 0 if inaccessible  

CREATE_BYTES  

NUMBER  

Size when created, in bytes  

NAME  

VARCHAR2  

Name of the file  

CREATION_CHANGE#  

NUMBER  

Change number at which the datafile was created  

CREATION_TIME  

DATE  

Timestamp of the datafile creation  

TS#  

NUMBER  

Tablespace number  

RFILE#  

NUMBER  

Tablespace relative datafile number  

LAST_CHANGE#  

NUMBER  

Last change# made to this datafile. Set to NULL if the datafile is being changed  

LAST_TIME  

DATE  

Timestamp of the last change  

OFFLINE_CHANGE#  

NUMBER  

Offline change# of the last offline range. This column is updated only when the datafile is brought online.  

ONLINE_CHANGE#  

NUMBER  

Online change# of the last offline range  

ONLINE_TIME  

DATE  

Online timestamp of the last offline range  

BLOCKS  

NUMBER  

Current datafile size in blocks; 0 if inaccessible  

BLOCK_SIZE  

NUMBER  

Block size of the datafile  

NAME  

VARCHAR2(512)  

Datafile name  

Table 3-2 describes values that can be entered in the ENABLED column.

Table 3-2: Values for the ENABLED Column
ENABLED Column Value   Description  

DISABLED  

No SQL access allowed  

READ ONLY  

No SQL updates allowed  

READ WRITE  

Full access allowed  

UNKNOWN  

Should not occur unless the control file is corrupted  

V$DATAFILE_COPY

This view displays datafile copy information from the controlfile.

Column   Datatype   Description  

RECID  

NUMBER  

Datafile copy record ID  

STAMP  

NUMBER  

Datafile copy record stamp  

NAME  

VARCHAR2(512)  

Filename of the datafile copy. The maximum length of the name is OS dependent  

TAG  

VARCHAR2(32)  

Datafile copy tag  

FILE#  

NUMBER  

Absolute datafile number  

RFILE#  

NUMBER  

Tablespace relative datafile number  

CREATION_CHANGE#  

NUMBER  

Datafile creation change#  

CREATION_TIME  

DATE  

Datafile creation timestamp  

RESETLOGS_CHANGE#  

NUMBER  

Resetlogs change# of the datafile when the copy was made  

RESETLOGS_TIME  

DATE  

Resetlogs timestamp of the datafile when the copy was made  

CHECKPOINT_CHANGE#  

NUMBER  

Checkpoint change# of the datafile when the copy was made  

CHECKPOINT_TIME  

DATE  

Checkpoint timestamp of the datafile when the copy was made  

ABSOLUTE_FUZZY
_CHANGE#  

NUMBER  

Highest change seen when the datafile was copied  

RECOVERY_FUZZY
_CHANGE#  

NUMBER  

Highest change written to the file by media recovery  

RECOVERY_FUZZY
_TIME  

DATE  

Timestamp of the highest change written to the file by media recovery  

ONLINE_FUZZY  

VARCHAR2(3)  

YES/NO. If set to YES, this is a copy taken using an operating system utility after a crash or offline immediate (or an invalid copy taken while datafile was online and the database open). Recovery will need to apply all redo up to the next crash recovery marker to make the file consistent.  

BACKUP_FUZZY  

VARCHAR2(3)  

YES/NO. If set to YES, this is a copy taken using the BEGIN BACKUP/END BACKUP technique. Recovery will need to apply all redo up to the end backup marker to make this copy consistent  

MARKED_CORRUPT  

NUMBER  

Number of blocks marked corrupt by this copy operation. That is, blocks that were not marked corrupted in the source datafile, but were detected and marked as corrupted during the copy operation.  

MEDIA_CORRUPT  

NUMBER  

Total number of media corrupt blocks. For example, blocks with checksum errors are marked media corrupt  

LOGICALLY_CORRUPT  

NUMBER  

Total number of logically corrupt blocks. For example, applying redo for unrecoverable operations will mark affected blocks logically corrupt.  

BLOCKS  

NUMBER  

Size of the datafile copy in blocks (also the size of the datafile when the copy was made)  

BLOCK_SIZE  

NUMBER  

Block size of the datafile  

OLDEST_OFFLINE
_RANGE  

NUMBER  

The RECID of the oldest offline range record in this controlfile copy. 0 for datafile copies  

COMPLETION_TIME  

DATE  

Time when the copy was completed  

DELETED  

VARCHAR2(3)  

YES/NO. If set to YES the datafile copy has been deleted or overwritten  

V$DATAFILE_HEADER

This view displays datafile information from the datafile headers.

Column   Datatype   Description  

FILE#  

NUMBER  

Datafile number (from controlfile)  

STATUS  

VARCHAR2(7)  

ONLINE/OFFLINE (from controlfile)  

ERROR  

VARCHAR2(18)  

NULL if the datafile header read and validation were successful. If the read failed then the rest of the columns are NULL. If the validation failed then the rest of columns may display invalid data. If there is an error then usually the datafile must be restored from a backup before it can be recovered or used.  

FORMAT  

NUMBER  

Indicates the format for the header block. The possible values are 6, 7, 8, or 0.

6 - indicates Oracle Version 6

7 - indicates Oracle Version 7

8 - indicates Oracle Version 8

0 - indicates the format could not be determined (for example, the header could not be read)  

RECOVER  

VARCHAR2(3)  

File needs media recovery YES/NO  

FUZZY  

VARCHAR2(3)  

File is fuzzy YES/NO  

CREATION_CHANGE#  

NUMBER  

Datafile creation change#  

CREATION_TIME  

DATE  

Datafile creation timestamp  

TABLESPACE_NAME  

VARCHAR2(30)  

Tablespace name  

TS#  

NUMBER  

Tablespace number  

RFILE#  

NUMBER  

Tablespace relative datafile number  

RESETLOGS_CHANGE#  

NUMBER  

Resetlogs change#  

RESETLOGS_TIME  

DATE  

Resetlogs timestamp  

CHECKPOINT_CHANGE#  

NUMBER  

Datafile checkpoint change#  

CHECKPOINT_TIME  

DATE  

Datafile checkpoint timestamp  

CHECKPOINT_COUNT  

NUMBER  

Datafile checkpoint count  

BYTES  

NUMBER  

Current datafile size in bytes  

BLOCKS  

NUMBER  

Current datafile size in blocks  

NAME  

VARCHAR2(512)  

Datafile name  

V$DBFILE

This view lists all datafiles making up the database. This view is retained for historical compatibility. Use of V$DATAFILE is recommended instead. For more information, see "V$DATAFILE" on page 3-23.

Column   Datatype   Description  

FILE#  

NUMBER  

File identifier  

NAME  

VARCHAR2  

Name of file  

V$DBLINK

This view describes all open database links (links with IN_TRANSACTION = YES). These database links must be committed or rolled back before being closed.

Column   Datatype   Description  

DB_LINK  

VARCHAR2(128)  

Name of the database link  

OWNER_ID  

NUMBER  

Owner of the database link UID  

LOGGED_ON  

VARCHAR2(3)  

Whether the database link is currently logged on  

HETEROGENEOUS  

VARCHAR2(3)  

Whether the database link is heterogeneous  

PROTOCOL  

VARCHAR2(6)  

Communication protocol for the database link  

OPEN_CURSORS  

NUMBER  

Whether there are open cursors for the database link  

IN_TRANSACTION  

VARCHAR2(3)  

Whether the database link is currently in a transaction  

UPDATE_SENT  

VARCHAR2(3)  

Whether there has been an update on the database link  

COMMIT_POINT
_STRENGTH  

NUMBER  

Commit point strength of the transactions on the database link  

V$DB_OBJECT_CACHE

This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

Column   Datatype   Description  

OWNER  

VARCHAR2  

Owner of the object  

NAME  

VARCHAR2  

Name of the object  

DB_LINK  

VARCHAR2  

Database link name, if any  

NAMESPACE  

VARCHAR2  

Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT  

TYPE  

VARCHAR2  

Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK  

SHARABLE_MEM  

NUMBER  

Amount of sharable memory in the shared pool consumed by the object  

LOADS  

NUMBER  

Number of times the object has been loaded. This count also increases when an object has been invalidated  

EXECUTIONS  

NUMBER  

Not used. To see actual execution counts, see "V$SQLAREA" on page 3-102.  

LOCKS  

NUMBER  

Number of users currently locking this object  

PINS  

NUMBER  

Number of users currently pinning this object  

KEPT  

VARCHAR2(3)  

YES or NO, depending on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP  

V$DB_PIPES

This view displays the pipes that are currently in this database.

Column   Datatype   Description  

OWNERID  

NUMBER  

The owner ID of the owner if this is a private pipe; NULL otherwise.  

NAME  

VARCHAR2  

The name of the pipe; for example, scott.pipe  

TYPE  

VARCHAR2  

PUBLIC or PRIVATE  

PIPE_SIZE  

NUMBER  

The amount of memory the pipe uses  

V$DELETED_OBJECT

This view displays information about deleted archived logs, datafile copies and backup pieces from the controlfile. The only purpose of this view is to optimize the recovery catalog resync operation. When an archived log, datafile copy, or backup piece is deleted, the corresponding record is marked deleted.

Column   Datatype   Description  

RECID  

NUMBER  

Deleted object record ID  

STAMP  

NUMBER  

Deleted object record stamp  

TYPE  

VARCHAR2(13)  

ARCHIVED LOG/DATAFILE COPY/BACKUP PIECE. Type of the deleted object  

OBJECT_RECID  

NUMBER  

Record ID of the deleted object  

OBJECT_STAMP  

NUMBER  

Record timestamp of the deleted object  

V$DISPATCHER

This view provides information on the dispatcher processes.

Column   Datatype   Description  

NAME  

VARCHAR2  

Name of the dispatcher process  

NETWORK  

VARCHAR2  

Network protocol supported by this dispatcher. For example, TCP or DECNET.  

PADDR  

RAW(4)  

Process address  

STATUS  

VARCHAR2  

Dispatcher status: WAIT (idle), SEND (sending a message connection), RECEIVE (receiving a message), CONNECT (establishing a connection), DISCONNECT (handling a disconnect request), BREAK (handling a break), OUTBOUND (establishing an outbound connection)  

ACCEPT  

VARCHAR2  

Whether this dispatcher is accepting new connections: YES, NO  

MESSAGES  

NUMBER  

Number of messages processed by this dispatcher  

BYTES  

NUMBER  

Size in bytes of messages processed by this dispatcher  

BREAKS  

NUMBER  

Number of breaks occurring in this connection  

OWNED  

NUMBER  

Number of circuits owned by this dispatcher  

CREATED  

NUMBER  

Number of circuits created by this dispatcher  

IDLE  

NUMBER  

Total idle time for this dispatcher in hundredths of a second  

BUSY  

NUMBER  

Total busy time for this dispatcher in hundredths of a second  

LISTENER  

NUMBER  

The most recent Oracle error number the dispatcher received from the listener  

V$DISPATCHER_RATE

This view provides rate statistics for the dispatcher processes.

Column   Datatype   Description  

NAME  

CHAR  

Process name  

PADDR  

RAW  

Process address  

CUR_LOOP_RATE  

NUMBER  

Current rate of loop events  

CUR_EVENT_RATE  

NUMBER  

Current rate of events  

CUR_EVENTS_PER_LOOP  

NUMBER  

Current events per loop  

CUR_MSG_RATE  

NUMBER  

Current rate of messages  

CUR_SVR_BUF_RATE  

NUMBER  

Current rate of buffers for the server  

CUR_SVR_BYTE_RATE  

NUMBER  

Current rate of bytes for the server  

CUR_SVR_BYTE_PER_BUF  

NUMBER  

Current bytes per buffer for the server  

CUR_CLT_BUF_RATE  

NUMBER  

Current rate of buffers for the client  

CUR_CLT_BYTE_RATE  

NUMBER  

Current rate of bytes for the client  

CUR_CLT_BYTE_PER_BUF  

NUMBER  

Current bytes per buffer for the client  

CUR_BUF_RATE  

NUMBER  

Current rate of buffers  

CUR_BYTE_RATE  

NUMBER  

Current rate of bytes  

CUR_BYTE_PER_BUF  

NUMBER  

Current bytes per buffer  

CUR_IN_CONNECT_RATE  

NUMBER  

Current inbound connects  

CUR_OUT_CONNECT_RATE  

NUMBER  

Current outbound connects  

CUR_RECONNECT_RATE  

NUMBER  

Current reconnects for connection pool and multiplexing  

MAX_LOOP_RATE  

NUMBER  

Maximum rate of loop events  

MAX_EVENT_RATE  

NUMBER  

Maximum rate of events  

MAX_EVENTS_PER_LOOP  

NUMBER  

Maximum events per loop  

MAX_MSG_RATE  

NUMBER  

Maximum rate of messages  

MAX_SVR_BUF_RATE  

NUMBER  

Maximum rate of buffers for the server  

MAX_SVR_BYTE_RATE  

NUMBER  

Maximum rate of bytes for the server  

MAX_SVR_BYTE_PER_BUF  

NUMBER  

Maximum number of bytes per buffer for the server  

MAX_CLT_BUF_RATE  

NUMBER  

Maximum rate of buffers for the client  

MAX_CLT_BYTE_RATE  

NUMBER  

Maximum rate of bytes for the client  

MAX_CLT_BYTE_PER_BUF  

NUMBER  

Maximum number of bytes per buffer for the client  

MAX_BUF_RATE  

NUMBER  

Maximum rate of buffers  

MAX_BYTE_RATE  

NUMBER  

Maximum rate of bytes  

MAX_BYTE_PER_BUF  

NUMBER  

Maximum number of bytes per buffer  

MAX_IN_CONNECT_RATE  

NUMBER  

Maximum number of inbound connects  

MAX_OUT_CONNECT_RATE  

NUMBER  

Maximum number of outbound connects  

MAX_RECONNECT_RATE  

NUMBER  

Maximum number of reconnects for connection pool and multiplexing  

AVG_LOOP_RATE  

NUMBER  

Average rate of loop events  

AVG_EVENT_RATE  

NUMBER  

Average rate of events  

AVG_EVENTS_PER_LOOP  

NUMBER  

Average events per loop  

AVG_MSG_RATE  

NUMBER  

Average rate of messages  

AVG_SVR_BUF_RATE  

NUMBER  

Average rate of buffers for the server  

AVG_SVR_BYTE_RATE  

NUMBER  

Average rate of bytes for the server  

AVG_SVR_BYTE_PER_BUF  

NUMBER  

Average bytes per buffer for the server  

AVG_CLT_BUF_RATE  

NUMBER  

Average rate of buffers for the client  

AVG_CLT_BYTE_RATE  

NUMBER  

Average rate of bytes for the client  

AVG_CLT_BYTE_PER_BUF  

NUMBER  

Average bytes per buffer for the client  

AVG_BUF_RATE  

NUMBER  

Average rate of buffers  

AVG_BYTE_RATE  

NUMBER  

Average rate of bytes  

AVG_BYTE_PER_BUF  

NUMBER  

Average bytes per buffer  

AVG_IN_CONNECT_RATE  

NUMBER  

Average inbound connects  

AVG_OUT_CONNECT_RATE  

NUMBER  

Average outbound connects  

AVG_RECONNECT_RATE  

NUMBER  

Average reconnects for connection pool and multiplexing  

NUM_LOOPS_TRACKED  

NUMBER  

Number of loop tracked  

NUM_MSG_TRACKED  

NUMBER  

Number of messages tracked  

NUM_SVR_BUF_TRACKED  

NUMBER  

Number of buffers for the server tracked  

NUM_CLT_BUF_TRACKED  

NUMBER  

Number of buffers for the client tracked  

NUM_BUF_TRACKED  

NUMBER  

Number of buffers tracked  

NUM_IN_CONNECT
_TRACKED  

NUMBER  

Number inbound connects tracked  

NUM_OUT_CONNECT
_TRACKED  

NUMBER  

Number outbound connects tracked  

NUM_RECONNECT
_TRACKED  

NUMBER  

Number of reconnects tracked  

SCALE_LOOPS  

NUMBER  

Scale of loop  

SCALE_MSG  

NUMBER  

Scale of messages  

SCALE_SVR_BUF  

NUMBER  

Scale of buffers for the server  

SCALE_CLT_BUF  

NUMBER  

Scale of buffers for the client  

SCALE_BUF  

NUMBER  

Scale of buffers  

SCALE_IN_CONNECT  

NUMBER  

Scale of inbound connects  

SCALE_OUT_CONNECT  

NUMBER  

Scale of outbound connects  

SCALE_RECONNECT  

NUMBER  

Scale of reconnects  

V$DLM_CONVERT_LOCAL

V$DLM_CONVERT_LOCAL displays the elapsed time for the local lock conversion operation.

Column   Datatype   Description  

INST_ID  

NUMBER  

ID of the instance  

CONVERT_TYPE  

VARCHAR2(64)  

Conversion types are listed in Table 3-3  

AVERAGE
_CONVERT_TIME  

NUMBER  

Average conversion time for each type of lock operation (in 100th of a second).  

CONVERT_COUNT  

NUMBER  

The number of operations.  

V$DLM_CONVERT_REMOTE

V$DLM_CONVERT_REMOTE displays the elapsed time for the remote lock conversion operation.

Column   Datatype   Description  

INST_ID  

NUMBER  

ID of the instance  

CONVERT_TYPE  

VARCHAR2(64)  

Conversion types are listed in Table 3-3  

AVERAGE_CONVERT
_TIME  

NUMBER  

Average conversion time for each type of lock operation (in 100th of a second).  

CONVERT_COUNT  

NUMBER  

The number of operations.  

Table 3-3: Values for the CONVERT_TYPE column
Conversion Type   Description  

NULL -> SS  

NULL mode to sub shared mode  

NULL -> SX  

NULL mode to shared exclusive mode  

NULL -> S  

NULL mode to shared mode  

NULL -> SSX  

NULL mode to sub-shared exclusive mode  

NULL -> X  

NULL mode to exclusive mode  

SS -> SX  

sub shared mode to shared exclusive mode  

SS -> S  

sub shared mode to shared mode  

SS -> SSX  

sub shared mode to sub-shared exclusive mode  

SS -> X  

sub shared mode to exclusive mode  

SX -> S  

shared exclusive mode to shared mode  

SX -> SSX  

shared exclusive mode to sub-shared exclusive mode  

SX -> X  

shared exclusive mode to exclusive mode  

S -> SX  

shared mode to shared exclusive mode  

S -> SSX  

shared mode to sub-shared exclusive mode  

S -> X  

shared mode to exclusive mode  

SSX -> X  

sub-shared exclusive mode to exclusive mode  

V$DLM_LATCH

V$DLM_LATCH displays statistics about DLM latch performance. The view includes totals for each type of latch rather than statistics for each individual latch. Ideally, the value IMM_GETS/TTL_GETS should be as close to 1 as possible.

Column   Datatype   Description  

LATCH_TYPE  

VARCHAR2(64)  

The name of the latch type. See Table 3-4.  

IMM_GETS  

NUMBER  

Immediate gets. The number of times that an attempt to acquire a latch of the specified type was satisfied immediately (that is, the process did not have to wait for another process to release the latch).  

TTL_GETS  

NUMBER  

Total gets. The total number of times the latch was acquired.  

Table 3-4: Values for the LATCH_TYPE column

deadlock list  

domain lock latch  

domain lock table latch  

domain table latch  

group lock latch  

group lock table latch  

group table freelist  

lock table freelist  

log/trace file latch  

proc hash list  

proc lock list  

proc table freelist  

rdomain record latch  

rdomain table latch  

resource hash list  

resource scan list  

resource structure  

resource table freelist  

shared comm. latch  

stat table latch  

sync data latch  

timeout list  

V$DLM_MISC

V$DLM_MISC displays miscellaneous DLM statistics.

Column   Datatype   Description  

STATISTIC#  

NUMBER  

Statistic number  

NAME  

VARCHAR2(64)  

Name of the statistic  

VALUE  

NUMBER  

Value associated with the statistic  

V$ENABLEDPRIVS

This view displays which privileges are enabled. These privileges can be found in the table SYS.SYSTEM_PRIVILEGES_MAP.

Column   Datatype   Description  

PRIV_NUMBER  

NUMBER  

Numeric identifier of enabled privileges  

V$ENQUEUE_LOCK

This view displays all locks owned by enqueue state objects. The columns in this view are identical to the columns in V$LOCK. For more information, see "V$LOCK" on page 3-51.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of lock state object  

KADDR  

RAW(4)  

Address of lock  

SID  

NUMBER  

Identifier for session holding or acquiring the lock  

TYPE  

VARCHAR2(2)  

Type of lock. For a list of user and system types that can have locks, see Table 3-5: "Values for the TYPE column: User Types" and Table 3-6: "Values for the TYPE column: System Types".  

ID1  

NUMBER  

Lock identifier #1 (depends on type)  

ID2  

NUMBER  

Lock identifier #2 (depends on type)  

LMODE  

NUMBER  

Lock mode in which the session holds the lock:
0, None
1, Null (NULL)
2, Row-S (SS)
3, Row-X (SX)
4, Share (S)
5, S/Row-X (SSX)
6, Exclusive (X)  

REQUEST  

NUMBER  

Lock mode in which the process requests the lock:
0, None
1, Null (NULL)
2, Row-S (SS)
3, Row-X (SX)
4, Share (S)
5, S/Row-X (SSX)
6, Exclusive (X)  

CTIME  

NUMBER  

Time since current mode was granted  

BLOCK  

NUMBER  

The lock is blocking another lock  

V$EVENT_NAME

This view contains information about wait events.

Column   Datatype   Description  

EVENT#  

NUMBER  

The number of the wait event  

NAME  

VARCHAR2(64)  

The name of the wait event  

PARAMETER1  

VARCHAR2(64)  

The description of the first parameter for the wait event  

PARAMETER2  

VARCHAR2(64)  

The description of the second parameter for the wait event  

PARAMETER3  

VARCHAR2(64)  

The description of the third parameter for the wait event  

V$EXECUTION

This view displays information on parallel query execution.

Column   Datatype   Description  

PID  

NUMBER  

Session ID  

DEPTH  

NUMBER  

The depth  

FUNCTION  

VARCHAR2(10)  

Session serial number  

TYPE  

VARCHAR2(7)  

Name of the OBJECT_NODE in plan table  

NVALS  

NUMBER  

Elapsed time for OBJECT_NODE  

VAL1  

NUMBER  

The value for number 1  

VAL2  

NUMBER  

The value for number 2  

SEQH  

NUMBER  

A sequence  

SEQL  

NUMBER  

A sequence  

V$EXECUTION_LOCATION

This view displays detailed information on the parallel query execution tree location.

Column   Datatype   Description  

SID  

NUMBER  

Session ID  

SERIAL#  

NUMBER  

Session serial number  

OBJECT_NODE  

VARCHAR2(20)  

Name of the OBJECT_NODE in plan table  

ELAPSED_TIME  

NUMBER  

Elapsed time for OBJECT_NODE  

V$FALSE_PING

V$FALSE_PING is a Parallel Server view. This view displays buffers that may be getting false pings. That is, buffers pinged more than 10 times that are protected by the same lock as another buffer that pinged more than 10 times. Buffers identified as getting false pings can be remapped in "GC_FILES_TO_LOCK" on page 1-44 to reduce lock collisions.

Column   Datatype   Description  

FILE#  

NUMBER  

Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES)  

BLOCK#  

NUMBER  

Block number  

STATUS  

VARCHAR2(1)  

Status of block:
FREE = not currently in use
XCUR = exclusive
SCUR = shared current
CR = consistent read
READ = being read from disk
MREC = in media recovery mode
IREC = in instance recovery mode  

XNC  

NUMBER  

Number of PCM lock conversions from Exclusive mode due to contention with another instance. This column is obsolete but is retained for historical compatibility.  

FORCED_READS  

NUMBER  

Number of times the block had to be reread from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on the block in exclusive mode  

FORCED_WRITES  

NUMBER  

Number of times DBWR had to write this block to disk because this instance had used the block and another instance had requested the lock on the block in a conflicting mode.  

NAME  

VARCHAR2(30)  

Name of the database object containing the block  

PARTITION_NAME  

VARCHAR2  

NULL for non-partitioned objects  

KIND  

VARCHAR2(12)  

Type of database object. See Table 3-1 on page 3-15.  

OWNER#  

NUMBER  

Owner number  

LOCK_ELEMENT_ADDR  

RAW(4)  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

LOCK_ELEMENT_NAME  

NUMBER  

The name of the lock that contains the PCM lock that is covering the buffer.  

LOCK_ELEMENT_CLASS  

NUMBER  

The lock element class  

For more information, see "GC_FILES_TO_LOCK" on page 1-44 and also Oracle8 Parallel Server Concepts & Administration.

V$FILE_PING

The view V$FILE_PING displays the number of blocks pinged per datafile. This information in turn can be used to determine access patterns to existing datafiles and deciding new mappings from datafile blocks to PCM locks.

Column   Datatype   Description  

FILE_NUMBER  

NUMBER  

Number of the datafile  

FREQUENCY  

NUMBER  

The frequency  

X_2_NULL  

NUMBER  

Number of lock conversions from Exclusive-to-NULL for all blocks in the file  

X_2_NULL_FORCED
_WRITE  

NUMBER  

Number of forced writes that occur for blocks of the specified file due to Exclusive-to-NULL conversions  

X_2_NULL_FORCED
_STALE  

NUMBER  

Number of times a block in the file was made STALE due to Exclusive-to-NULL conversions  

X_2_S  

NUMBER  

Number of lock conversions from Exclusive-to-Shared for all blocks in the file  

X_2_S_FORCED_WRITE  

NUMBER  

Number of forced writes that occur for blocks of the specified file due to Exclusive-to-Shared conversions  

X_2_SSX  

NUMBER  

Number of lock conversions from Exclusive-to-Sub Shared Exclusive for all blocks in the file  

X_2_SSX_FORCED
_WRITE  

NUMBER  

Number of forced writes that occur for blocks of the specified file due to Exclusive-to-Sub Shared Exclusive conversions  

S_2_NULL  

NUMBER  

Number of lock conversions from Shared-to-NULL for all blocks in the file  

S_2_NULL_FORCED
_STALE  

NUMBER  

Number of times a block in the file was made STALE due to Shared-to-NULL conversions  

SS_2_NULL  

NUMBER  

Number of lock conversions from Sub Shared-to-NULL for all blocks in the file  

WRB  

NUMBER  

Number of times the instance received a write single buffer cross instance call for this file  

WRB_FORCED_WRITE  

NUMBER  

Number of blocks written due to write single buffer cross instance calls for this file  

RBR  

NUMBER  

Number of times the instance received a resuse block range cross instance call for this file  

RBR_FORCED_WRITE  

NUMBER  

Number of blocks written due to resuse block range cross instance calls for this file  

RBR_FORCED_STALE  

NUMBER  

Number of times a block in this file was made STALE due to resuse block range cross instance calls  

CBR  

NUMBER  

Number of times the instance received a checkpoint block range cross instance call for this file  

CBR_FORCED_WRITE  

NUMBER  

Number of blocks in this file which were written due to checkpoint cross range cross instance calls  

NULL_2_X  

NUMBER  

Number of lock conversions from NULL-to-Exclusive for all blocks of the specified file  

S_2_X  

NUMBER  

Number of lock conversions from Shared-to-Exclusive for all blocks of the specified file  

SSX_2_X  

NUMBER  

Number of lock conversions from Sub Shared Exclusive-to-Exclusive for all blocks of the specified file  

NULL_2_S  

NUMBER  

Number of lock conversions from NULL-to-Shared for all blocks of the specified file  

NULL_2_SS  

NUMBER  

Number of lock conversions from NULL-to-Sub Shared for all blocks of the specified file  

V$FILESTAT

This view contains information about file read/write statistics.

Column   Datatype   Description  

FILE#  

NUMBER  

Number of the file  

PHYRDS  

NUMBER  

Number of physical reads done  

PHYWRTS  

NUMBER  

Number of times DBWR is required to write  

PHYBLKRD  

NUMBER  

Number of physical blocks read  

PHYBLKWRT  

NUMBER  

Number of blocks written to disk; which may be the same as PHYWRTS if all writes are single blocks  

READTIM  

NUMBER  

Time (in milliseconds) spent doing reads if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE  

WRITETIM  

NUMBER  

Time (in milliseconds) spent doing writes if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE  

V$FIXED_TABLE

This view displays all dynamic performance tables, views, and derived tables in the database.

Column   Datatype   Description  

NAME  

VARCHAR2(30)  

Name of the object  

OBJECT_ID  

NUMBER  

Identifier of the fixed object  

TYPE  

VARCHAR2(5)  

Object type: TABLE, VIEW  

TABLE_NUM  

NUMBER  

Number that identifies the dynamic performance table if it is of type TABLE  

V$FIXED_VIEW_DEFINITION

This view contains the definitions of all the fixed views (views beginning with V$). Use this table with caution. Oracle tries to keep the behavior of fixed views the same from release to release, but the definitions of the fixed views can change without notice. Use these definitions to optimize your queries by using indexed columns of the dynamic performance tables.

Column   Datatype   Description  

VIEW_NAME  

VARCHAR2(30)  

The name of the fixed view  

VIEW_DEFINITION  

VARCHAR2(2000)  

The definition of the fixed view  

V$GLOBAL_TRANSACTION

This view displays information on the currently active global transactions.

Column   Datatype   Description  

FORMATID  

NUMBER  

Format identifier of the global transaction  

GLOBALID  

RAW(64)  

Global transaction identifier of the global transaction  

BRANCHID  

RAW(64)  

Branch qualifier of the global transaction  

BRANCHES  

NUMBER  

Total number of branches in the global transaction  

REFCOUNT  

NUMBER  

Number of siblings for this global transaction, must be the same as branches.  

PREPARECOUNT  

NUMBER  

Number of branches of the global transaction that have prepared  

STATE  

VARCHAR2(18)  

State of the branch of the global transaction  

FLAGS  

NUMBER  

The numerical representation of the state  

COUPLING  

VARCHAR2(15)  

Whether the branches are loosely coupled or tightly coupled  

V$INDEXED_FIXED_COLUMN

This view displays the columns in dynamic performance tables that are indexed (X$ tables). The X$ tables can change without notice. Use this view only to write queries against fixed views (V$ views) more efficiently.

Column   Datatype   Description  

TABLE_NAME  

VARCHAR2(30)  

The name of the dynamic performance table that is indexed  

INDEX_NUMBER  

NUMBER  

Number that distinguishes to which index a column belongs  

COLUMN_NAME  

VARCHAR2(30)  

Name of the column that is being indexed  

COLUMN_POSITION  

NUMBER  

Position of the column in the index key (this is mostly relevant for multicolumn indexes)  

V$INSTANCE

This view displays the state of the current instance. This version of V$INSTANCE is not compatible with earlier versions of V$INSTANCE.

Column   Datatype   Description  

INSTANCE_NUMBER  

NUMBER  

Instance number used for instance registration . Corresponds to INSTANCE_NUMBER initialization parameter.

See "INSTANCE_NUMBER" on page 1-49.  

INSTANCE_NAME  

VARCHAR2(16)  

Instance name.  

HOST_NAME  

VARCHAR2(64)  

Name of the host machine  

VERSION  

VARCHAR2(17)  

RDBMS version  

STARTUP_TIME  

DATE  

Time when instance was started up  

STATUS  

VARCHAR2(7)  

STARTED/MOUNTED/OPEN

STARTED after startup nomount

MOUNTED after startup mount or alter database close

OPEN after startup or after database open  

PARALLEL  

VARCHAR2(3)  

YES/NO in parallel server mode  

THREAD#  

NUMBER  

Redo thread opened by the instance  

ARCHIVER  

VARCHAR2(7)  

STOPPED/STARTED/FAILED FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes  

LOG_SWITCH_WAIT  

VARCHAR2(11)  

ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then value is NULL  

LOGINS  

VARCHAR2(10)  

ALLOWED/RESTRICTED  

SHUTDOWN_PENDING  

VARCHAR2(3)  

YES/NO  

V$LATCH

This view lists statistics for non-parent latches and summary statistics for parent latches. That is, the statistics for a parent latch include counts from each of its children.

Note: Columns SLEEP5, SLEEP6,... SLEEP11 are present for compatibility with previous versions of Oracle. No data are accumulated for these columns.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of latch object  

LATCH#  

NUMBER  

Latch number  

LEVEL#  

NUMBER  

Latch level  

NAME  

VARCHAR2(64)  

Latch name  

GETS  

NUMBER  

Number of times obtained a wait  

MISSES  

NUMBER  

Number of times obtained a wait but failed on the first try  

SLEEPS  

NUMBER  

Number of times slept when wanted a wait  

IMMEDIATE_GETS  

NUMBER  

Number of times obtained without a wait  

IMMEDIATE_MISSES  

NUMBER  

Number of times failed to get without a wait  

WAITERS_WOKEN  

NUMBER  

How many times a wait was awakened  

WAITS_HOLDING
_LATCH  

NUMBER  

Number of waits while holding a different latch  

SPIN_GETS  

NUMBER  

Gets that missed first try but succeeded on spin  

SLEEP1  

NUMBER  

Waits that slept 1 time  

SLEEP2  

NUMBER  

Waits that slept 2 times  

SLEEP3  

NUMBER  

Waits that slept 3 times  

SLEEP4  

NUMBER  

Waits that slept 4 times  

SLEEP5  

NUMBER  

Waits that slept 5 times  

SLEEP6  

NUMBER  

Waits that slept 6 times  

SLEEP7  

NUMBER  

Waits that slept 7 times  

SLEEP8  

NUMBER  

Waits that slept 8 times  

SLEEP9  

NUMBER  

Waits that slept 9 times  

SLEEP10  

NUMBER  

Waits that slept 10 times  

SLEEP11  

NUMBER  

Waits that slept 11 times  

V$LATCHHOLDER

This view contains information about the current latch holders.

Column   Datatype   Description  

PID  

NUMBER  

Identifier of process holding the latch  

SID  

NUMBER  

Identifier of the session that owns the latch  

LADDR  

RAW(4)  

Latch address  

NAME  

VARCHAR2  

Name of latch being held  

V$LATCHNAME

This view contains information about decoded latch names for the latches shown in V$LATCH. The rows of V$LATCHNAME have a one-to-one correspondence to the rows of V$LATCH. For more information, see "V$LATCH" on page 3-46.

Column   Datatype   Description  

LATCH#  

NUMBER  

Latch number  

NAME  

VARCHAR2(64)  

Latch name  

V$LATCH_CHILDREN

This view contains statistics about child latches. This view includes all columns of V$LATCH plus the CHILD# column. Note that child latches have the same parent if their LATCH# columns match each other. For more information, see "V$LATCH" on page 3-46.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of latch object  

LATCH#  

NUMBER  

Latch number for a parent latch  

CHILD#  

NUMBER  

Child number of a parent latch shown in LATCH#  

LEVEL#  

NUMBER  

Latch level  

NAME  

VARCHAR2(64)  

Latch name  

GETS  

NUMBER  

Number of times obtained a wait  

MISSES  

NUMBER  

Number of times obtained a wait but failed on the first try  

SLEEPS  

NUMBER  

Number of times slept when wanted a wait  

IMMEDIATE_GETS  

NUMBER  

Number of times obtained without a wait  

IMMEDIATE_MISSES  

NUMBER  

Number of time failed to get without a wait  

WAITERS_WOKEN  

NUMBER  

How many times a wait was awakened  

WAITS_HOLDING
_LATCH  

NUMBER  

Number of waits while holding a different latch  

SPIN_GETS  

NUMBER  

Gets that missed first try but succeeded on spin  

SLEEPn  

NUMBER  

Waits that slept n times  

V$LATCH_MISSES

This view contains statistics about missed attempts to acquire a latch.

Column   Datatype   Description  

PARENT_NAME  

VARCHAR2  

Latch name of a parent latch  

WHERE  

VARCHAR2  

Location that attempted to acquire the latch  

NWFAIL_COUNT  

NUMBER  

Number of times that no-wait acquisition of the latch failed  

SLEEP_COUNT  

NUMBER  

Number of times that acquisition attempts caused sleeps  

V$LATCH_PARENT

This view contains statistics about the parent latch. The columns of V$LATCH_PARENT are identical to those in V$LATCH. For more information, see "V$LATCH" on page 3-46.

V$LIBRARYCACHE

This view contains statistics about library cache performance and activity.

Column   Datatype   Description  

NAMESPACE  

VARCHAR2(15)  

The library cache namespace  

GETS  

NUMBER  

The number of times a lock was requested for objects of this namespace  

GETHITS  

NUMBER  

The number of times an object's handle was found in memory  

GETHITRATIO  

NUMBER  

The ratio of GETHITS to GETS  

PINS  

NUMBER  

The number of times a PIN was requested for objects of this namespace  

PINHITS  

NUMBER  

The number of times all of the meta data pieces of the library object were found in memory  

PINHITRATIO  

NUMBER  

The ratio of PINHITS to PINS  

RELOADS  

NUMBER  

Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk  

INVALIDATIONS  

NUMBER  

The total number of times objects in this namespace were marked invalid because a dependent object was modified  

DLM_LOCK_REQUESTS  

NUMBER  

The number of GET requests lock instance locks  

DLM_PIN_REQUESTS  

NUMBER  

The number of PIN requests lock instance locks  

DLM_PIN_RELEASES  

NUMBER  

The number of release requests PIN instance locks  

DLM_INVALIDATION
_REQUESTS  

NUMBER  

The number of GET requests for invalidation instance locks  

DLM_INVALIDATIONS  

NUMBER  

The number of invalidation pings received from other instances  

V$LICENSE

This view contains information about license limits.

Column   Datatype   Description  

SESSIONS_MAX  

NUMBER  

Maximum number of concurrent user sessions allowed for the instance  

SESSIONS_WARNING  

NUMBER  

Warning limit for concurrent user sessions for the instance  

SESSIONS_CURRENT  

NUMBER  

Current number of concurrent user sessions  

SESSIONS
_HIGHWATER  

NUMBER  

Highest number of concurrent user sessions since the instance started  

USERS_MAX  

NUMBER  

Maximum number of named users allowed for the database  

V$LOADCSTAT

This view contains SQL*Loader statistics compiled during the execution of a direct load. These statistics apply to the whole load. Any SELECT against this table results in "no rows returned" since you cannot load data and do a query at the same time.

Column   Datatype   Description  

READ  

NUMBER  

Number of records read  

REJECTED  

NUMBER  

Number of records rejected  

TDISCARD  

NUMBER  

Total number of discards during the load  

NDISCARD  

NUMBER  

Number of discards from the current file  

SAVEDATA  

NUMBER  

Whether save data points are used  

V$LOADTSTAT

SQL*Loader statistics compiled during the execution of a direct load. These statistics apply to the current table. Any SELECT against this table results in "no rows returned" since you cannot load data and do a query at the same time.

Column   Datatype   Description  

LOADED  

NUMBER  

Number of records loaded  

REJECTED  

NUMBER  

Number of records rejected  

FAILWHEN  

NUMBER  

Number of records that failed to meet any WHEN clause  

ALLNULL  

NUMBER  

Number of records that were completely null and were therefore not loaded  

LEFT2SKIP  

NUMBER  

Number of records yet to skip during a continued load  

V$LOCK

This view lists the locks currently held by the Oracle Server and outstanding requests for a lock or latch.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of lock state object  

KADDR  

RAW(4)  

Address of lock  

SID  

NUMBER  

Identifier for session holding or acquiring the lock  

TYPE  

VARCHAR2(2)  

Type of lock. For a list of user and system types that can have locks, see Table 3-5: "Values for the TYPE column: User Types" and Table 3-6: "Values for the TYPE column: System Types".  

ID1  

NUMBER  

Lock identifier #1 (depends on type)  

ID2  

NUMBER  

Lock identifier #2 (depends on type)  

LMODE  

NUMBER  

Lock mode in which the session holds the lock:
0, None
1, Null (NULL)
2, Row-S (SS)
3, Row-X (SX)
4, Share (S)
5, S/Row-X (SSX)
6, Exclusive (X)  

REQUEST  

NUMBER  

Lock mode in which the process requests the lock:
0, None
1, Null (NULL)
2, Row-S (SS)
3, Row-X (SX)
4, Share (S)
5, S/Row-X (SSX)
6, Exclusive (X)  

CTIME  

NUMBER  

Time since current mode was granted  

BLOCK  

NUMBER  

The lock is blocking another lock  

The locks on the user types in Table 3-5 are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.

Table 3-5: Values for the TYPE column: User Types
User Type   Description  

TM  

DML enqueue  

TX  

Transaction enqueue  

UL  

User supplied  

The locks on the system types in Table 3-6 are held for extremely short periods of time.

Table 3-6: Values for the TYPE column: System Types
System Type   Description  

BL  

Buffer hash table instance  

CF  

Control file schema global enqueue  

CI  

Cross-instance function invocation instance  

CU  

Cursor bind  

DF  

Data file instance  

DL  

Direct loader parallel index create  

DM  

Mount/startup db primary/secondary instance  

DR  

Distributed recovery process  

DX  

Distributed transaction entry  

FS  

File set  

HW  

Space management operations on a specific segment  

IN  

Instance number  

IR  

Instance recovery serialization global enqueue  

IS  

Instance state  

IV  

Library cache invalidation instance  

JQ  

Job queue  

KK  

Thread kick  

LA .. LP  

Library cache lock instance lock (A..P = namespace)  

MM  

Mount definition global enqueue  

MR  

Media recovery  

NA..NZ  

Library cache pin instance (A..Z = namespace)  

PF  

Password File  

PI, PS  

Parallel operation  

PR  

Process startup  

QA..QZ  

Row cache instance (A..Z = cache)  

RT  

Redo thread global enqueue  

SC  

System commit number instance  

SM  

SMON  

SN  

Sequence number instance  

SQ  

Sequence number enqueue  

SS  

Sort segment  

ST  

Space transaction enqueue  

SV  

Sequence number value  

TA  

Generic enqueue  

TS  

Temporary segment enqueue (ID2=0)  

TS  

New block allocation enqueue (ID2=1)  

TT  

Temporary table enqueue  

UN  

User name  

US  

Undo segment DDL  

WL  

Being-written redo log instance  

V$LOCK_ACTIVITY

This is a Parallel Server view. V$LOCK_ACTIVITY displays the DLM lock operation activity of the current instance. Each row corresponds to a type of lock operation.

Column   Datatype   Description  

FROM_VAL  

VARCHAR2(4)  

PCM lock initial state:
NULL
S
X
SSX  

TO_VAL  

VARCHAR2(4)  

PCM lock initial state:
NULL
S
X
SSX  

ACTION_VAL  

VARCHAR2(51)  

Description of lock conversions
Lock buffers for read
Lock buffers for write
Make buffers CR (no write)
Upgrade read lock to write
Make buffers CR (write dirty buffers)
Downgrade write lock to read (write dirty buffers)
Write transaction table/undo blocks
Transaction table/undo blocks (write dirty buffers)
Make transaction table/undo blocks available share
Rearm transaction table write mechanism  

COUNTER  

NUMBER  

Number of times the lock operation executed  

For more information, see Oracle8 Parallel Server Concepts & Administration.

V$LOCK_ELEMENT

This is a Parallel Server view. There is one entry in v$LOCK_ELEMENT for each PCM lock that is used by the buffer cache. The name of the PCM lock that corresponds to a lock element is {`BL', indx, class}.

Column   Datatype   Description  

LOCK_ELEMENT_ ADDR  

RAW(4)  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

LOCK_ELEMENT_NAME  

NUMBER  

The name of the lock that contains the PCM lock that is covering the buffer.  

INDX  

NUMBER  

Platform specific lock manager identifier  

CLASS  

NUMBER  

Platform specific lock manager identifier  

MODE_HELD  

NUMBER  

Platform dependent value for lock mode held; often:
3 = share
5 = exclusive  

BLOCK_COUNT  

NUMBER  

Number of blocks covered by PCM lock  

RELEASING  

NUMBER  

Non-zero if PCM lock is being downgraded  

ACQUIRING  

NUMBER  

Non-zero if PCM lock is being upgraded  

INVALID  

NUMBER  

Non-zero if PCM lock is invalid. (A lock may become invalid after a system failure.)  

FLAGS  

NUMBER  

Process level flags for the LE  

For more information, see Oracle8 Parallel Server Concepts & Administration.

V$LOCKED_OBJECT

This view lists all locks acquired by every transaction on the system.

Column   Datatype   Description  

XIDUSN  

NUMBER  

Undo segment number  

XIDSLOT  

NUMBER  

Slot number  

XIDSQN  

NUMBER  

Sequence number  

OBJECT_ID  

NUMBER  

Object ID being locked  

SESSION_ID  

NUMBER  

Session ID  

ORACLE_USERNAME  

VARCHAR2(30)  

Oracle user name  

OS_USER_NAME  

VARCHAR2(15)  

OS user name  

PROCESS  

VARCHAR2(9)  

OS process ID  

LOCKED_MODE  

NUMBER  

Lock mode  

V$LOCKS_WITH_COLLISIONS

This is a Parallel Server view. Use this view to find the locks that protect multiple buffers, each of which has been either force-written or force-read at least 10 times. It is very likely that those buffers are experiencing false pings due to being mapped to the same lock.

Column   Datatype   Description  

LOCK_ELEMENT_ ADDR  

RAW(4)  

The address of the lock element that contains the PCM lock covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

For more information, see Oracle8 Parallel Server Concepts & Administration.

V$LOG

This view contains log file information from the control files.

Column   Datatype   Description  

GROUP#  

NUMBER  

Log group number  

THREAD#  

NUMBER  

Log thread number  

SEQUENCE#  

NUMBER  

Log sequence number  

BYTES  

NUMBER  

Size of the log in bytes  

MEMBERS  

NUMBER  

Number of members in the log group  

ARCHIVED  

VARCHAR2  

Archive status: YES, NO  

STATUS  

VARCHAR2(16)  

Log status. The STATUS column can have the values in Table 3-7.  

FIRST_CHANGE#  

NUMBER  

Lowest SCN in the log  

FIRST_TIME  

DATE  

Time of first SCN in the log  

Table 3-7 describes values in the log STATUS column.

Table 3-7: Values for the STATUS column
STATUS   Meaning  

UNUSED  

Indicates the online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.  

CURRENT  

Indicates this is the current redo log. This implies that the redo log is active. The redo log could be open or closed.  

ACTIVE  

Indicates the log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived.  

CLEARING  

Indicates the log is being recreated as an empty log after an ALTER DATABASE CLEAR LOGFILE command. After the log is cleared, the status changes to UNUSED.  

CLEARING
_CURRENT  

Indicates that the current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an
I/O error writing the new log header.  

INACTIVE  

Indicates the log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.  

V$LOGFILE

This view contains information about redo log files.

Column   Datatype   Description  

GROUP#  

NUMBER  

Redo log group identifier number  

STATUS  

VARCHAR2  

Status of this log member: INVALID (file is inaccessible), STALE (file's contents are incomplete), DELETED (file is no longer used), or blank (file is in use)  

MEMBER  

VARCHAR2  

Redo log member name  

V$LOGHIST

This view contains log history information from the control file. This view is retained for historical compatibility. Use of V$LOG_HISTORY is recommended instead. For more information, see "V$LOG_HISTORY" on page 3-59.

Column   Datatype   Description  

THREAD#  

NUMBER  

Log thread number  

SEQUENCE#  

NUMBER  

Log sequence number  

FIRST_CHANGE#  

NUMBER  

Lowest SCN in the log  

FIRST_TIME  

DATE  

Time of first SCN in the log  

SWITCH_CHANGE#  

NUMBER  

SCN at which the log switch occurred; one more than highest SCN in the log  

V$LOG_HISTORY

This view contains log history information from the control file.

Column   Datatype   Description  

THREAD#  

NUMBER  

Thread number of the archived log  

SEQUENCE#  

NUMBER  

Sequence number of the archived log  

FIRST_TIME  

DATE  

Time of first entry (lowest SCN) in the log. This column was previously named TIME.  

FIRST_CHANGE#  

NUMBER  

Lowest SCN in the log. This column was previously named LOW_CHANGE#.  

NEXT_CHANGE#  

NUMBER  

Highest SCN in the log. This column was previously named HIGH_CHANGE#.  

RECID  

NUMBER  

Controlfile record ID  

STAMP  

NUMBER  

Controlfile record stamp  

V$MLS_PARAMETERS

This is a Trusted Oracle Server view that lists Trusted Oracle Server-specific initialization parameters. For more information, see the Trusted Oracle Server Administrator's Guide.

V$MTS

This view contains information for tuning the multi-threaded server.

Column   Datatype   Description  

MAXIMUM
_CONNECTIONS  

NUMBER  

The maximum number of connections each dispatcher can support. This value is determined at startup time using Net8 constants and other port-specific information, or can be lowered using the mls_dispatchers parameter.  

SERVERS
_STARTED  

NUMBER  

The total number of multi-threaded servers started since the instance started (but not including those started during startup)  

SERVERS
_TERMINATED  

NUMBER  

The total number of multi-threaded servers stopped by Oracle since the instance started  

SERVERS
_HIGHWATER  

NUMBER  

The highest number of servers running at one time since the instance started. If this value reaches the value set for the MTS_MAX_SERVERS initialization parameter, consider raising the value of MTS_SERVERS. For more information, see "MTS_SERVERS" on page 1-75.  

V$MYSTAT

This view contains statistics on the current session.

Column   Datatype   Description  

SID  

NUMBER  

The ID of the current session  

STATISTIC#  

NUMBER  

The number of the statistic  

VALUE  

NUMBER  

The value of the statistic  

V$NLS_PARAMETERS

This view contains current values of NLS parameters.

Column   Datatype   Description  

PARAMETER  

VARCHAR2  

Parameter name:
NLS_CALENDAR
NLS_CHARACTERSET
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_ISO_CURRENCY
NLS_LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY  

VALUE  

VARCHAR2  

NLS parameter value  

V$NLS_VALID_VALUES

This view lists all valid values for NLS parameters.

Column   Datatype   Description  

PARAMETER  

VARCHAR2(64)  

Parameter name:
NLS_LANGUAGE
NLS_SORT
NLS_TERRITORY
NLS_CHARACTERSET  

VALUE  

VARCHAR2(64)  

NLS parameter value  

V$OBJECT_DEPENDENCY

This view can be used to determine what objects are depended on by a package, procedure, or cursor that is currently loaded in the shared pool. For example, together with V$SESSION and V$SQL, it can be used to determine which tables are used in the SQL statement that a user is currently executing. For more information, see "V$SESSION" on page 3-78 and "V$SQL" on page 3-96.

Column   Datatype   Description  

FROM_ADDRESS  

RAW(4)  

The address of a procedure, package, or cursor that is currently loaded in the shared pool  

FROM_HASH  

NUMBER  

The hash value of a procedure, package, or cursor that is currently loaded in the shared pool  

TO_OWNER  

VARCHAR2(64)  

The owner of the object that is depended on  

TO_NAME  

VARCHAR2(1000)  

The name of the object that is depended on  

TO_ADDRESS  

RAW(4)  

The address of the object that is depended on. These can be used to look up more information on the object in V$DB_OBJECT_CACHE.  

TO_HASH  

NUMBER  

The hash value of the object that is depended on. These can be used to look up more information on the object in V$DB_OBJECT_CACHE.  

TO_TYPE  

NUMBER  

The type of the object that is depended on  

V$OFFLINE_RANGE

This view displays datafile offline information from the controlfile. Note that the last offline range of each datafile is kept in the DATAFILE record. For more information, see "V$DATAFILE" on page 3-23.

An offline range is created for a datafile when its tablespace is first ALTERed to be OFFLINE NORMAL or READ ONLY, and then subsequently ALTERed to be ONLINE or read-write. Note that no offline range is created if the datafile itself is ALTERed to be OFFLINE or if the tablespace is ALTERed to be OFFLINE IMMEDIATE.

Column   Datatype   Description  

RECID  

NUMBER  

Record ID  

STAMP  

NUMBER  

Record stamp  

FILE#  

NUMBER  

Datafile number  

OFFLINE_CHANGE#  

NUMBER  

SCN at which offlined  

ONLINE_CHANGE#  

NUMBER  

SCN at which onlined  

ONLINE_TIME  

DATE  

Time of offline SCN  

V$OPEN_CURSOR

This view lists cursors that each user session currently has opened and parsed.

Column   Datatype   Description  

SADDR  

RAW  

Session address  

SID  

NUMBER  

Session identifier  

USER_NAME  

VARCHAR2(30)  

User that is logged in to the session  

ADDRESS  

RAW  

Used with HASH_VALUE to identify uniquely the SQL statement being executed in the session  

HASH_VALUE  

NUMBER  

Used with ADDRESS to identify uniquely the SQL statement being executed in the session  

SQL_TEXT  

VARCHAR2(60)  

First 60 characters of the SQL statement that is parsed into the open cursor  

V$OPTION

This view lists options that are installed with the Oracle Server.

Column   Datatype   Description  

PARAMETER  

VARCHAR2(64)  

The name of the option  

VALUE  

VARCHAR2(64)  

TRUE if the option is installed  

V$PARAMETER

This view lists information about initialization parameters.

Column   Datatype   Description  

NUM  

NUMBER  

Parameter number  

NAME  

VARCHAR2(64)  

Parameter name  

TYPE  

NUMBER  

Parameter type; 1 = Boolean,
2 = string, 3 = integer  

VALUE  

VARCHAR2(512)  

Parameter value  

ISDEFAULT  

VARCHAR2(9)  

Whether the parameter value is the default  

ISSES_MODIFIABLE  

VARCHAR2(5)  

TRUE = the parameter can be changed with ALTER SESSION

FALSE= the parameter cannot be changed with ALTER SESSION  

ISSYS_MODIFIABLE  

VARCHAR2(9)  

IMMEDIATE = the parameter can be changed with ALTER SYSTEM

DEFERRED=the parameter cannot be changed until the next session

FALSE= the parameter cannot be changed with ALTER SYSTEM  

ISMODIFIED  

VARCHAR2(10)  

Indicates how the parameter was modified. If an ALTER SESSION was performed, the value will be MODIFIED. If an ALTER SYSTEM (which will cause all the currently logged in sessions' values to be modified) was performed the value will be SYS_MODIFIED.  

ISADJUSTED  

VARCHAR2(5)  

Indicates that the rdbms adjusted the input value to a more suitable value (e.g., the parameter value should be prime, but the user input a non-prime number, so the rdbms adjusted the value to the next prime number)  

DESCRIPTION  

VARCHAR2(64)  

A descriptive comment about the parameter  

V$PING

This is a Parallel Server view. The V$PING view is identical to the V$CACHE view but only displays blocks that have been pinged at least once. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects. For more information, see "V$CACHE" on page 3-14.

Column   Datatype   Description  

FILE#  

NUMBER  

Datafile identifier number (to find filename, query "DBA_DATA_FILES" on page 2-55 or "V$DBFILE" on page 3-28)  

BLOCK#  

NUMBER  

Block number  

CLASS#  

NUMBER  

Class number  

STATUS  

VARCHAR2(4)  

Status of block:
FREE= not currently in use
XCUR= exclusive
SCUR= shared current
CR= consistent read
READ= being read from disk
MREC= in media recovery mode
IREC= in instance recovery mode  

XNC  

NUMBER  

Number of PCM lock conversions due to contention with another instance. This column is obsolete but is retained for historical compatibility  

FORCED_READS  

NUMBER  

Number of times the block had to be reread from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on the block in exclusive mode  

FORCED_WRITES  

NUMBER  

Number of times DBWR had to write this block to disk because this instance had used the block and another instance had requested the lock on the block in a conflicting mode.  

NAME  

VARCHAR2(30)  

Name of the database object containing the block  

PARTITION_NAME  

VARCHAR2(30)  

NULL for non-partitioned objects  

KIND  

VARCHAR2(15)  

Type of database object. See Table 3-1 on page 3-15.  

OWNER#  

NUMBER  

Owner number  

LOCK_ELEMENT
_ ADDR  

RAW(4)  

The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.  

LOCK_ELEMENT
_NAME  

NUMBER  

The name of the lock that contains the PCM lock that is covering the buffer.  

For more information, see Oracle8 Parallel Server Concepts & Administration.

V$PQ_SESSTAT

This view lists session statistics for parallel queries.

Column   Datatype   Description  

STATISTIC  

VARCHAR2(30)  

Name of the statistic. See Table 3-8 on page 3-66  

LAST_QUERY  

NUMBER  

The value of the statistic for the last operation  

SESSION_TOTAL  

NUMBER  

The value of the statistic for the entire session to this point in time  

The statistics (fixed rows) in Table 3-8 have been defined for this view. After you have run a query or DML operation, you can use the information derived from V$PQ_SESSTAT to view the number of slave processes used, and other information for the session and system.

Table 3-8: Names of Statistics in the STATISTIC Column
Statistic (Fixed Row)   Description  

Queries Parallelized  

Number of queries that were run in parallel  

DML Parallelized  

Number of DML operations that were run in parallel.  

DFO Trees  

Number of executed DFO trees  

Server Threads  

Total number of parallel servers used  

Allocation Height  

Requested number of servers per instance  

Allocation Width  

Requested number of instances  

Local Msgs Sent  

Number of local (intra-instance) messages sent  

Distr Msgs Sent  

Number of remote (inter-instance) messages sent  

Local Msgs Recv'd  

Number of local (intra-instance) messages received  

Distr Msgs Recv'd  

Number of remote (inter-instance) messages received  

V$PQ_SLAVE

This view lists statistics for each of the active parallel query servers on an instance.

Column   Datatype   Description  

SLAVE_NAME  

VARCHAR2(4)  

Name of the parallel query server  

STATUS  

VARCHAR2(4)  

The current status of the parallel query server (BUSY or IDLE)  

SESSIONS  

NUMBER  

The number of sessions that have used this parallel query server  

IDLE_TIME_CUR  

NUMBER  

The amount of time spent idle while processing statements in the current session  

BUSY_TIME_CUR  

NUMBER  

The amount of time spent busy while processing statements in the current session  

CPU_SECS_CUR  

NUMBER  

The amount of CPU time spent on the current session  

MSGS_SENT_CUR  

NUMBER  

The number of messages sent while processing statements for the current session  

MSGS_RCVD_CUR  

NUMBER  

The number of messages received while processing statements for the current session  

IDLE_TIME_TOTAL  

NUMBER  

The total amount of time this query server has been idle  

BUSY_TIME_TOTAL  

NUMBER  

The total amount of time this query server has been active  

CPU_SECS_TOTAL  

NUMBER  

The total amount of CPU time this query server has used to process statements  

MSGS_SENT_TOTAL  

NUMBER  

The total number of messages this query server has sent  

MSGS_RCVD_TOTAL  

NUMBER  

The total number of messages this query server has received  

V$PQ_SYSSTAT

This view lists system statistics for parallel queries.

Column   Datatype   Description  

STATISTIC  

VARCHAR2(30)  

Name of the statistic See Table 3-9 on page 3-68  

VALUE  

NUMBER  

The value of the statistic  

The statistics (fixed rows) in Table 3-9 have been defined for this view. After you have run a query or DML operation, you can use the information derived from V$PQ_SYSSTAT to view the number of slave processes used, and other information for the system.

Table 3-9: Names of Statistics in the STATISTIC Column
Statistic (Fixed Row)   Description  

Servers Busy  

Number of currently busy servers on this instance  

Servers Idle  

Number of currently idle servers on this instance  

Servers Highwater  

Number of active servers on this instance that have partaken in >= 1 operation so far  

Server Sessions  

Total number of operations executed in all servers on this instance  

Servers Started  

Total number of servers started on this instance  

Servers Shutdown  

Total number of servers shutdown on this instance  

Servers Cleaned Up  

Total number of servers on this instance cleaned up due to process death  

Queries Initiated  

Total number of parallel queries initiated on this instance  

DML Initiated  

Total number of parallel DML operations that were initiated  

DFO Trees  

Total number of DFO trees executed on this instance  

Local Msgs Sent  

Total number of local (intra-instance) messages sent on this instance  

Distr Msgs Sent  

Total number of remote (inter-instance) messages sent on this instance  

Local Msgs Recv'd  

Total number of remote (inter-instance) messages received on this instance  

Distr Msgs Recv'd  

Total number of remote (inter-instance) messages received on this instance  

V$PQ_TQSTAT

This view contains statistics on parallel query operations. The statistics are compiled after the query completes and only remain for the duration of the session. It displays the number of rows processed through each parallel query server at each stage of the execution tree. This view can help determine skew problems in a query's execution.

Column   Datatype   Description  

DFO_NUMBER  

NUMBER  

The data flow operator (DFO) tree number to differentiate queries  

TQ_ID  

NUMBER  

The table queue ID within the query, which represents the connection between two DFO nodes in the query execution tree  

SERVER_TYPE  

VARCHAR2(10)  

The role in table queue - producer/consumer/ranger  

NUM_ROWS  

NUMBER  

The number of rows produced/consumed  

BYTES  

NUMBER  

The number of bytes produced/consumed  

OPEN_TIME  

NUMBER  

Time (secs) the table queue remained open  

AVG_LATENCY  

NUMBER  

Time (ms) for a message to be dequeued after it enters the queue  

WAITS  

NUMBER  

The number of waits encountered during dequeue  

TIMEOUTS  

NUMBER  

The number of timeouts when waiting for a message  

PROCESS  

VARCHAR2(10)  

Process ID  

INSTANCE  

NUMBER  

Instance ID  

V$PROCESS

This view contains information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of process state object  

PID  

NUMBER  

Oracle process identifier  

SPID  

VARCHAR2  

Operating system process identifier  

USERNAME  

VARCHAR2  

Operating system process username. Any Two-Task user coming across the network has "-T" appended to the username.  

SERIAL#  

NUMBER  

Process serial number  

TERMINAL  

VARCHAR2  

Operating system terminal identifier  

PROGRAM  

VARCHAR2  

Program in progress  

BACKGROUND  

VARCHAR2  

1 for a background process; NULL for a normal process  

LATCHWAIT  

VARCHAR2  

Address of latch the process is waiting for; NULL if none  

LATCHSPIN  

VARCHAR2  

Address of latch the process is being spun on; NULL if none  

V$PWFILE_USERS

This view lists users who have been granted SYSDBA and SYSOPER privileges as derived from the password file.

Column   Datatype   Description  

USERNAME  

VARCHAR2(30)  

The name of the user that is contained in the password file.  

SYSDBA  

VARCHAR2(5)  

If the value of this column is TRUE, the user can connect with SYSDBA privileges.  

SYSOPER  

VARCHAR2(5)  

If the value of this column is TRUE, the user can connect with SYSOPER privileges  

V$QUEUE

This view contains information on the multi-thread message queues.

Column   Datatype   Description  

PADDR  

RAW(4)  

Address of the process that owns the queue  

TYPE  

VARCHAR2  

Type of queue: COMMON (processed by servers), OUTBOUND (used by remote servers), DISPATCHER.  

QUEUED  

NUMBER  

Number of items in the queue  

WAIT  

NUMBER  

Total time that all items in this queue have waited. Divide by TOTALQ for average wait per item.  

TOTALQ  

NUMBER  

Total number of items that have ever been in the queue  

V$RECOVER_FILE

This view displays the status of files needing media recovery.

Column   Datatype   Description  

FILE#  

NUMBER  

File identifier number  

ONLINE  

VARCHAR2  

Online status: ONLINE, OFFLINE  

ERROR  

VARCHAR2  

Why the file needs to be recovered: NULL if reason unknown, or OFFLINE NORMAL if recovery not needed  

CHANGE#  

NUMBER  

SCN where recovery must start  

TIME  

DATE  

Time of SCN when recovery must start  

V$RECOVERY_FILE_STATUS

V$RECOVERY_FILE_STATUS contains one row for each datafile for each RECOVER command. This view contains useful information only for the Oracle process doing the recovery. When Recovery Manager directs a server process to perform recovery, only Recovery Manager is able to view the relevant information in this view. V$RECOVERY_FILE_STATUS will be empty to all other Oracle users.

Column   Datatype   Description  

FILENUM  

NUMBER  

The number of the file being recovered  

FILENAME  

VARCHAR2(257)  

The filename of the datafile being recovered  

STATUS  

VARCHAR2(13)  

The status of the recovery. Contains one of the following values:
IN RECOVERY
CURRENT
NOT RECOVERED  

For further information, see The Oracle8 Server Backup and Recovery Guide.

V$RECOVERY_LOG

This view lists information about archived logs that are needed to complete media recovery. This information is derived from the log history view, V$LOG_HISTORY. For more information, see "V$LOG_HISTORY" on page 3-59.

V$RECOVERY_LOG contains useful information only for the Oracle process doing the recovery. When Recovery Manager directs a server process to perform recovery, only Recovery Manager is able to view the relevant information in this view. V$RECOVERY_LOG will be empty to all other Oracle users.

Column   Datatype   Description  

THREAD#  

NUMBER  

Thread number of the archived log  

SEQUENCE#  

NUMBER  

Sequence number of the archived log  

TIME  

VARCHAR2  

Time of first entry (lowest SCN) in the log  

ARCHIVE_NAME  

VARCHAR2  

Name of the file when archived, using the naming convention specified by "LOG_ARCHIVE_FORMAT" on page 1-59.  

For further information, see The Oracle8 Server Backup and Recovery Guide.

V$RECOVERY_STATUS

V$RECOVERY_STATUS contains statistics of the current recovery process. This view contains useful information only for the Oracle process doing the recovery. When Recovery Manager directs a server process to perform recovery, only Recovery Manager is able to view the relevant information in this view. V$RECOVERY_STATUS will be empty to all other Oracle users.

Column   Datatype   Description  

RECOVERY_CHECKPOINT  

DATE  

The point in time to which the recovery has occurred. If no logs have been applied, this is the point in time the recovery starts.  

THREAD  

NUMBER  

The number of the redo thread currently being processed.  

SEQUENCE_NEEDED  

NUMBER  

Log sequence number of the log needed by the recovery process. The value is 0 if no log is needed.  

SCN_NEEDED  

VARCHAR2(16)  

The low SCN of the log needed by recovery. The value is 0 if unknown or no log is needed.  

TIME_NEEDED  

DATE  

Time when the log was created. The value is midnight on 1/1/88 if the time is unknown or if no log is needed.  

PREVIOUS_LOG_NAME  

VARCHAR2(257)  

The filename of the log.  

PREVIOUS_LOG_STATUS  

VARCHAR2(13)  

The status of the previous log. Contains one of the following values:
RELEASE
WRONG NAME
MISSING NAME
UNNEEDED NAME
NONE  

REASON  

VARCHAR2(13)  

The reason recovery is returning control to the user. Contains one of the following values:
NEED LOG
LOG REUSED
THREAD DISABLED  

For further information, see The Oracle8 Server Backup and Recovery Guide.

V$REQDIST

This view lists statistics for the histogram of MTS dispatcher request times, divided into 12 buckets, or ranges of time. The time ranges grow exponentially as a function of the bucket number.

Column   Datatype   Description  

BUCKET  

NUMBER  

Bucket number: 0 - 11; the maximum time for each bucket is (4 * 2^N)/100 seconds  

COUNT  

NUMBER  

Count of requests whose total time to complete (excluding wait time) falls in this range  

V$RESOURCE

This view contains resource name and address information.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of resource object  

TYPE  

VARCHAR2  

Resource type. The resource types are listed in Table 3-5: "Values for the TYPE column: User Types" and Table 3-6: "Values for the TYPE column: System Types"  

ID1  

NUMBER  

Resource identifier #1  

ID2  

NUMBER  

Resource identifier #2  

V$RESOURCE_LIMIT

This view displays information about global resource use for some of the system resources. Use this view to monitor the consumption of resources so that you can take corrective action, if necessary. Many of the resources correspond to initialization parameters listed in Table 3-10.

Some resources, those used by DLM for example, have an initial allocation (soft limit), and the hard limit, which is theoretically infinite (although in practice it is limited by SGA size). During SGA reservation/initialization, a place is reserved in SGA for the INITIAL_ALLOCATION of resources, but if this allocation is exceeded, additional resources are allocated up to the value indicated by LIMIT_VALUE. The CURRENT_UTILIZATION column indicates whether the initial allocation has been exceeded. When the initial allocation value is exceeded, the additional required resources are allocated from the shared pool, where they must compete for space with other resources.

A good choice for the value of INITIAL_ALLOCATION will avoid the contention for space. For most resources, the value for INITIAL_ALLOCATION is the same as the LIMIT_VALUE. Exceeding LIMIT_VALUE results in an error.

Column   Datatype   Description  

RESOURCE_NAME  

VARCHAR2(30)  

Name of the resource (see Table 3-10)  

CURRENT
_UTILIZATION  

NUMBER  

Number of (resources, locks, or processes) currently being used  

MAX_UTILIZATION  

NUMBER  

Maximum consumption of this resource since the last instance start-up  

INITIAL_ALLOCATION  

VARCHAR2(10)  

Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file. (UNLIMITED for infinite allocation)  

LIMIT_VALUE  

VARCHAR2(10)  

Unlimited for resources and locks. This can be greater than the initial allocation value. (UNLIMITED for infinite limit)  

Table 3-10: Values for RESOURCE_NAME column
Resource Name   Corresponds to this Initialization Parameter  

DISTRIBUTED_TRANSACTIONS  

DISTRIBUTED_TRANSACTIONS For more information on this parameter, see "DISTRIBUTED_TRANSACTIONS" on page 1-39.  

DML_LOCKS  

DML_LOCKS For more information on this parameter see "DML_LOCKS" on page 1-39.  

ENQUEUE_LOCKS  

This value is computed by Oracle. Use the V$ENQUEUE_LOCK view (described on page 3-37) to obtain more information about the enqueue locks.  

ENQUEUE_RESOURCES  

ENQUEUE_RESOURCES For more information on this parameter see "ENQUEUE_RESOURCES" on page 1-41.  

LM_PROCESSES  

LM_PROCS For more information on this parameter see "LM_PROCS" on page 1-55.  

LM_RESOURCES  

LM_RESS For more information on this parameter see "LM_RESS" on page 1-55.  

LM_LOCKS  

LM_LOCKS For more information on this parameter see "LM_LOCKS" on page 1-54.  

MTS_MAX_SERVERS  

MTS_MAX_SERVERS For more information on this parameter see "MTS_MAX_SERVERS" on page 1-72.  

PARALLEL_SLAVES  

PARALLEL_MAX_SERVERS For more information on this parameter, see "PARALLEL_MAX_SERVERS" on page 1-89.  

PROCESSES  

PROCESSES For more information on this parameter, see "PROCESSES" on page 1-95.  

ROLLBACK_SEGMENTS  

MAX_ROLLBACK_SEGMENTS For more information on this parameter, see "MAX_ROLLBACK_SEGMENTS" on page 1-67.  

SESSIONS  

SESSIONS For more information on this parameter, see "SESSIONS" on page 1-103.  

SORT_SEGMENT_LOCKS  

This value is computed by Oracle  

TEMPORARY_LOCKS  

This value is computed by Oracle  

TRANSACTIONS  

TRANSACTIONS For more information on this parameter, see "TRANSACTIONS" on page 1-114.  

V$ROLLNAME

This view lists the names of all online rollback segments. This view can only be accessed when the database is open.

Column   Datatype   Description  

USN  

NUMBER  

Rollback (undo) segment number  

NAME  

VARCHAR2  

Rollback segment name  

V$ROLLSTAT

This view contains rollback segment statistics.

Column   Datatype   Description  

USN  

NUMBER  

Rollback segment number  

EXTENTS  

NUMBER  

Number of extents in rollback segment  

RSSIZE  

NUMBER  

Size in bytes of rollback segment  

WRITES  

NUMBER  

Number of bytes written to rollback segment  

XACTS  

NUMBER  

Number of active transactions  

GETS  

NUMBER  

Number of header gets  

WAITS  

NUMBER  

Number of header waits  

OPTSIZE  

NUMBER  

Optimal size of rollback segment  

HWMSIZE  

NUMBER  

High water mark of rollback segment size  

SHRINKS  

NUMBER  

Number of times the size of a rollback segment decreases  

WRAPS  

NUMBER  

Number of times rollback segment is wrapped  

EXTENDS  

NUMBER  

Number of times rollback segment size is extended  

AVESHRINK  

NUMBER  

Average shrink size  

AVEACTIVE  

NUMBER  

Current size of active extents, averaged over time.  

STATUS  

VARCHAR2(15)  

Rollback segment status  

CUREXT  

NUMBER  

Current extent  

CURBLK  

NUMBER  

Current block  

V$ROWCACHE

This view displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache.

Column   Datatype   Description  

CACHE#  

NUMBER  

Row cache ID number  

TYPE  

VARCHAR2  

Parent or subordinate row cache type  

SUBORDINATE#  

NUMBER  

Subordinate set number  

PARAMETER  

VARCHAR2  

Name of the initialization parameter that determines the number of entries in the data dictionary cache  

COUNT  

NUMBER  

Total number of entries in the cache  

USAGE  

NUMBER  

Number of cache entries that contain valid data  

FIXED  

NUMBER  

Number of fixed entries in the cache  

GETS  

NUMBER  

Total number of requests for information on the data object  

GETMISSES  

NUMBER  

Number of data requests resulting in cache misses  

SCANS  

NUMBER  

Number of scan requests  

SCANMISSES  

NUMBER  

Number of times a scan failed to find the data in the cache  

SCANCOMPLETES  

NUMBER  

For a list of subordinate entries, the number of times the list was scanned completely  

MODIFICATIONS  

NUMBER  

Number of inserts, updates, and deletions  

FLUSHES  

NUMBER  

Number of times flushed to disk  

V$SESSION

This view lists session information for each current session.

Column   Datatype   Description  

SADDR  

RAW(4)  

Session address  

SID  

NUMBER  

Session identifier  

SERIAL#  

NUMBER  

Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.  

AUDSID  

NUMBER  

Auditing session ID  

PADDR  

RAW(4)  

Address of the process that owns this session  

USER#  

NUMBER  

Oracle user identifier  

USERNAME  

VARCHAR2(30)  

Oracle username  

COMMAND  

NUMBER  

Command in progress (last statement parsed); for a list of values, see Table 3-11 on page 3-81  

OWNERID  

NUMBER  

The column contents are invalid if the value is 2147483644. Otherwise, this column contains the identifier of the user who owns the migratable session.  

TADDR  

VARCHAR2(8)  

Address of transaction state object  

LOCKWAIT  

VARCHAR2(8)  

Address of lock waiting for; NULL if none  

STATUS  

VARCHAR2(8)  

Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA), SNIPED (session inactive, waiting on the client)  

SERVER  

VARCHAR2(9)  

Server type: DEDICATED, SHARED, PSEUDO, NONE  

SCHEMA#  

NUMBER  

Schema user identifier  

SCHEMANAME  

VARCHAR2(30)  

Schema user name  

OSUSER  

VARCHAR2(15)  

Operating system client user name  

PROCESS  

VARCHAR2(9)  

Operating system client process ID  

MACHINE  

VARCHAR2(64)  

Operating system machine name  

TERMINAL  

VARCHAR2(10)  

Operating system terminal name  

PROGRAM  

VARCHAR2(48)  

Operating system program name  

TYPE  

VARCHAR2(10)  

Session type  

SQL_ADDRESS  

RAW(4)  

Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed  

SQL_HASH_VALUE  

NUMBER  

Used with SQL_ADDRESS to identify the SQL statement that is currently being executed  

MODULE  

VARCHAR2(48)  

Contains the name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure.  

MODULE_HASH  

NUMBER  

The hash value of the above MODULE  

ACTION  

VARCHAR2(32)  

Contains the name of the currently executing action as set by calling the
DBMS_APPLICATION_INFO.SET_ACTION procedure.  

ACTION_HASH  

NUMBER  

The hash value of the above action name  

CLIENT_INFO  

VARCHAR2(64)  

Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure.  

FIXED_TABLE
_SEQUENCE  

NUMBER  

This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.  

ROW_WAIT_OBJ#  

NUMBER  

Object ID for the table containing the ROWID specified in ROW_WAIT_ROW#  

ROW_WAIT_FILE#  

NUMBER  

Identifier for the datafile containing the ROWID specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero.  

ROW_WAIT
_BLOCK#  

NUMBER  

Identifier for the block containing the ROWID specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero.  

ROW_WAIT_ROW#  

NUMBER  

The current ROWID being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero.  

PDML_ENABLED  

VARCHAR2(3)  

If set to YES, the session is in a PARALLEL DML enabled mode, otherwise set to NO  

FAILOVER_TYPE  

VARCHAR2(10)  

NONE if failover is disabled for this session, SESSION if client is able to failover its session following a disconnect, and SELECT if client is able to fail over selects in progress as well  

FAILOVER
_METHOD  

VARCHAR2(3)  

NONE if failover is disabled for this session, BASIC if client reconnects following a disconnect, PRECONNECT if the backup instance is able to support all connections from every instance that it is backup for  

FAILED_OVER  

VARCHAR2(13)  

TRUE if running in failover mode and have failed over, otherwise FALSE  

Table 3-11 lists numeric values corresponding to commands that may be in progress during a session. These values can appear in the V$SESSION COMMAND column. They also appear in the data dictionary view SYS.AUDIT_ACTIONS.

Table 3-11: Command Numbers for the COMMAND Column
Command
Number
 
Command  

0  

No command in progress. Occurs when process is in a transitory state, usually when terminating.  

1  

CREATE TABLE  

2  

INSERT  

3  

SELECT  

4  

CREATE CLUSTER  

5  

ALTER CLUSTER  

6  

UPDATE  

7  

DELETE  

8  

DROP CLUSTER  

9  

CREATE INDEX  

10  

DROP INDEX  

11  

ALTER INDEX  

12  

DROP TABLE  

13  

CREATE SEQUENCE  

14  

ALTER SEQUENCE  

15  

ALTER TABLE  

16  

DROP SEQUENCE  

17  

GRANT  

18  

REVOKE  

19  

CREATE SYNONYM  

20  

DROP SYNONYM  

21  

CREATE VIEW  

22  

DROP VIEW  

23  

VALIDATE INDEX  

24  

CREATE PROCEDURE  

25  

ALTER PROCEDURE  

26  

LOCK TABLE  

27  

NO OPERATION  

28  

RENAME  

29  

COMMENT  

30  

AUDIT  

31  

NOAUDIT  

32  

CREATE DATABASE LINK  

33  

DROP DATABASE LINK  

34  

CREATE DATABASE  

35  

ALTER DATABASE  

36  

CREATE ROLLBACK SEGMENT  

37  

ALTER ROLLBACK SEGMENT  

38  

DROP ROLLBACK SEGMENT  

39  

CREATE TABLESPACE  

40  

ALTER TABLESPACE  

41  

DROP TABLESPACE  

42  

ALTER SESSION  

43  

ALTER USER  

44  

COMMIT  

45  

ROLLBACK  

46  

SAVEPOINT  

47  

PL/SQL EXECUTE  

48  

SET TRANSACTION  

49  

ALTER SYSTEM SWITCH LOG  

50  

EXPLAIN  

51  

CREATE USER  

52  

CREATE ROLE  

53  

DROP USER  

54  

DROP ROLE  

55  

SET ROLE  

56  

CREATE SCHEMA  

57  

CREATE CONTROL FILE  

58  

ALTER TRACING  

59  

CREATE TRIGGER  

60  

ALTER TRIGGER  

61  

DROP TRIGGER  

62  

ANALYZE TABLE  

63  

ANALYZE INDEX  

64  

ANALYZE CLUSTER  

65  

CREATE PROFILE  

66  

DROP PROFILE  

67  

ALTER PROFILE  

68  

DROP PROCEDURE  

69  

DROP PROCEDURE  

70  

ALTER RESOURCE COST  

71  

CREATE SNAPSHOT LOG  

72  

ALTER SNAPSHOT LOG  

73  

DROP SNAPSHOT LOG  

74  

CREATE SNAPSHOT  

75  

ALTER SNAPSHOT  

76  

DROP SNAPSHOT  

79  

ALTER ROLE  

85  

TRUNCATE TABLE  

86  

TRUNCATE CLUSTER  

88  

ALTER VIEW  

91  

CREATE FUNCTION  

92  

ALTER FUNCTION  

93  

DROP FUNCTION  

94  

CREATE PACKAGE  

95  

ALTER PACKAGE  

96  

DROP PACKAGE  

97  

CREATE PACKAGE BODY  

98  

ALTER PACKAGE BODY  

99  

DROP PACKAGE BODY  

V$SESSION_CONNECT_INFO

This view displays information about network connections for the current session.

Column   Datatype   Description  

SID  

NUMBER  

Session identifier (can be used to join this view with V$SESSION)  

AUTHENTICATION
_TYPE  

VARCHAR2(15)  

How the user was authenticated: OS, PROTOCOL, or NETWORK.  

OSUSER  

VARCHAR2(30)  

The external username for this database user  

NETWORK
_SERVICE_BANNER  

VARCHAR2(2000)  

Product banners for each Net8 service used for this connection (one row per banner)  

V$SESSION_CURSOR_CACHE

This view displays information on cursor usage for the current session. Note: the V$SESSION_CURSOR_CACHE view is not a measure of the effectiveness of the SESSION_CACHED_CURSORS initialization parameter.

Column   Datatype   Description  

MAXIMUM  

NUMBER  

Maximum number of cursors to cache. Once you hit this number, some cursors will need to be closed in order to open more. The value in this column is derived from the initialization parameter OPEN_CURSORS.  

COUNT  

NUMBER  

The current number of cursors (whether they are in use or not)  

OPENED_ONCE  

NUMBER  

Number of cursors opened at least once  

OPEN  

NUMBER  

Current number of open cursors  

OPENS  

NUMBER  

Cumulative total of cursor opens minus one. This is because the cursor that is currently open and being used for this query is not counted in the OPENS statistic.  

HITS  

NUMBER  

Cumulative total of cursor open hits  

HIT_RATIO  

NUMBER  

Ratio of the number of times an open cursor was found divided by the number of times a cursor was sought  

V$SESSION_EVENT

This view lists information on waits for an event by a session. Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance. For more information, see "TIMED_STATISTICS" on page 1-113.

Column   Datatype   Description  

SID  

NUMBER  

The ID of the session  

EVENT  

VARCHAR2(64)  

The name of the wait event. For more information, see Appendix A, "Oracle Wait Events"  

TOTAL_WAITS  

NUMBER  

The total number of waits for this event by this session  

TOTAL_TIMEOUTS  

NUMBER  

The total number of timeouts for this event by this session  

TIME_WAITED  

NUMBER  

The total amount of time waited for this event by this session, in hundredths of a second  

AVERAGE_WAIT  

NUMBER  

The average amount of time waited for this event by this session, in hundredths of a second  

V$SESSION_LONGOPS

This view displays the status of certain long-running operations. It provides progression reports on operations using the columns SOFAR and TOTALWORK. For example, the operational status for the following components can be monitored:

V$SESSION_OBJECT_CACHE

This view displays object cache statistics for the current user session on the local server (instance).

Column  

Datatype  

Description  

PINS  

NUMBER  

Number of object pins or look-ups in the cache  

HITS  

NUMBER  

Number of object pins that found the object already in the cache  

TRUE_HITS  

NUMBER  

Number of object pins that found the object already in the cache and in the desired state (thus, not requiring refresh from the database)  

HIT_RATIO  

NUMBER  

The ratio of HITS/PINS  

TRUE_HIT_RATIO  

NUMBER  

The ratio of TRUE_HITS/PINS  

OBJECT_REFRESHES  

NUMBER  

Number of objects in the cache that were refreshed with a new value from the database  

CACHE_REFRESHES  

NUMBER  

Number of times the whole cache (all objects) were refreshed  

OBJECT_FLUSHES  

NUMBER  

Number of objects in the cache that were flushed to the database  

CACHE_FLUSHES  

NUMBER  

Number of times the whole cache (all objects) were flushed to the database  

CACHE_SHRINKS  

NUMBER  

Number of times the cache was shrunk to the optimal size  

CACHED_OBJECTS  

NUMBER  

Number of objects currently cached  

PINNED_OBJECTS  

NUMBER  

Number of objects currently pinned  

CACHE_SIZE  

NUMBER  

Current size of the cache in bytes  

OPTIMAL_SIZE  

NUMBER  

Optimal size of the cache in bytes  

MAXIMUM_SIZE  

NUMBER  

Maximum size of the cache in bytes  

V$SESSION_WAIT

This view lists the resources or events for which active sessions are waiting.

The following are tuning considerations:

In previous releases the WAIT_TIME column contained an arbitrarily large value instead of a negative value to indicate the platform did not have a fast timing mechanism.

For more information on session waits, see Appendix A, "Oracle Wait Events".

Column   Datatype   Description  

SID  

NUMBER  

Session identifier  

SEQ#  

NUMBER  

Sequence number that uniquely identifies this wait. Incremented for each wait.  

EVENT  

VARCHAR2(64)  

Resource or event for which the session is waiting. For more information, see Appendix A, "Oracle Wait Events"  

P1TEXT  

VARCHAR2  

Description of first additional parameter  

P1  

NUMBER  

First additional parameter  

P1RAW  

RAW(4)  

First additional parameter  

P2TEXT  

VARCHAR2  

Description of second parameter  

P2  

NUMBER  

Second additional parameter  

P2RAW  

RAW(4)  

Second additional parameter  

P3TEXT  

VARCHAR2  

Description of third parameter  

P3  

NUMBER  

Third additional parameter  

P3RAW  

RAW(4)  

Third additional parameter  

WAIT_TIME  

NUMBER  

A non-zero value is the session's last wait time. A zero value means the session is currently waiting.  

STATE  

VARCHAR2  

Wait state (see Table 3-12)  

Table 3-12 defines values in the V$SESSION_WAIT STATE column.

Table 3-12: Wait State listed in the STATE Column
STATE   Value   Meaning  

WAITING  

0  

The session is currently waiting  

WAITED UNKNOWN TIME  

-2  

Duration of last wait is unknown  

WAITED SHORT TIME  

-1  

Last wait < 1/100th of a second  

WAITED KNOWN TIME  

>0  

WAIT_TIME = duration of last wait  

V$SESSTAT

This view lists user session statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), see "V$STATNAME" on page 3-105.

Column   Datatype   Description  

SID  

NUMBER  

Session identifier  

STATISTIC#  

NUMBER  

Statistic number (identifier)  

VALUE  

NUMBER  

Statistic value  

V$SESS_IO

This view lists I/O statistics for each user session.

Column   Datatype   Description  

SID  

NUMBER  

Session identifier  

BLOCK_GETS  

NUMBER  

Block gets for this session  

CONSISTENT_GETS  

NUMBER  

Consistent gets for this session  

PHYSICAL_READS  

NUMBER  

Physical reads for this session  

BLOCK_CHANGES  

NUMBER  

Block changes for this session  

CONSISTENT
_CHANGES  

NUMBER  

Consistent changes for this session  

V$SGA

This view contains summary information on the System Global Area.

Column   Datatype   Description  

NAME  

VARCHAR2  

SGA component group  

VALUE  

NUMBER  

Memory size in bytes  

V$SGASTAT

This view contains detailed information on the System Global Area.

Column   Datatype   Description  

NAME  

VARCHAR2  

SGA component name  

BYTES  

NUMBER  

Memory size in bytes  

POOL  

VARCHAR2  

Designates the pool in which the memory in NAME resides. Value can be

LARGE POOL - Memory is allocated from the large pool

or

SHARED POOL - Memory is allocated from the shared pool  

V$SHARED_POOL_RESERVED

This fixed view lists statistics that help you tune the reserved pool and space within the shared pool.

The following columns of V$SHARED_POOL_RESERVED are valid only if the initialization parameter shared_pool_reserved_size is set to a valid value. For more information, see "SHARED_POOL_RESERVED_SIZE" on page 1-104.

Column   Datatype   Description  

FREE_SPACE  

NUMBER  

Total amount of free space on the reserved list  

AVG_FREE_SIZE  

NUMBER  

Average size of the free memory on the reserved list  

FREE_COUNT  

NUMBER  

Number of free pieces of memory on the reserved list  

MAX_FREE_SIZE  

NUMBER  

Size of the largest free piece of memory on the reserved list  

USED_SPACE  

NUMBER  

Total amount of used memory on the reserved list  

AVG_USED_SIZE  

NUMBER  

Average size of the used memory on the reserved list  

USED_COUNT  

NUMBER  

Number of used pieces of memory on the reserved list  

MAX_USED_SIZE  

NUMBER  

Size of the largest used piece of memory on the reserved list  

REQUESTS  

NUMBER  

Number of times that the reserved list was searched for a free piece of memory  

REQUEST_MISSES  

NUMBER  

Number of times the reserved list did not have a free piece of memory to satisfy the request, and started flushing objects from the LRU list  

LAST_MISS_SIZE  

NUMBER  

Request size of the last request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list  

MAX_MISS_SIZE  

NUMBER  

Request size of the largest request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list  

The following columns of V$SHARED_POOL_RESERVED contain values which are valid even if shared_pool_reserved_size is not set.

Column   Datatype   Description  

REQUEST_FAILURES  

NUMBER  

Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-4031 occurred)  

LAST_FAILURE_SIZE  

NUMBER  

Request size of the last failed request (that is, the request size for the last ORA-4031 error)  

ABORTED_REQUEST _THRESHOLD  

NUMBER  

Minimum size of a request which signals an ORA-4031 error without flushing objects  

ABORTED_REQUESTS  

NUMBER  

Number of requests that signalled an ORA-4031 error without flushing objects  

LAST_ABORTED_SIZE  

NUMBER  

Last size of the request that returned an ORA-4031 error without flushing objects from the LRU list  

V$SHARED_SERVER

This view contains information on the shared server processes.

Column   Datatype   Description  

NAME  

VARCHAR2  

Name of the server  

PADDR  

RAW(4)  

Server's process address  

STATUS  

VARCHAR2  

Server status:
EXEC (executing SQL)
WAIT (ENQ) (waiting for a lock),
WAIT (SEND) (waiting to send data to user)
WAIT (COMMON) (idle; waiting for a user request)
WAIT (RESET) (waiting for a circuit to reset after a break)
QUIT (terminating)  

MESSAGES  

NUMBER  

Number of messages processed  

BYTES  

NUMBER  

Total number of bytes in all messages  

BREAKS  

NUMBER  

Number of breaks  

CIRCUIT  

RAW(4)  

Address of circuit currently being serviced  

IDLE  

NUMBER  

Total idle time in hundredths of a second  

BUSY  

NUMBER  

Total busy time in hundredths of a second  

REQUESTS  

NUMBER  

Total number of requests taken from the common queue in this server's lifetime  

V$SORT_SEGMENT

This view contains information about every sort segment in a given instance.

Column   Datatype   Description  

TABLESPACE_NAME  

VARCHAR2(31)  

Name of tablespace  

SEGMENT_FILE  

NUMBER  

File number of the first extent  

SEGMENT_BLOCK  

NUMBER  

Block number of the first extent  

EXTENT_SIZE  

NUMBER  

Extent size  

CURRENT_USERS  

NUMBER  

Number of active users of the segment  

TOTAL_EXTENTS  

NUMBER  

Total number of extents in the segment  

TOTAL_BLOCKS  

NUMBER  

Total number of blocks in the segment  

RELATIVE_FNO  

NUMBER  

Relative file number of the sort segment header  

USED_EXTENTS  

NUMBER  

Extents allocated to active sorts  

USED_BLOCKS  

NUMBER  

Blocks allocated to active sorts  

FREE_EXTENTS  

NUMBER  

Extents not allocated to any sort  

FREE_BLOCKS  

NUMBER  

Blocks not allocated to any sort  

ADDED_EXTENTS  

NUMBER  

Number of extent allocations  

EXTENT_HITS  

NUMBER  

Number of times an unused extent was found in the pool  

FREED_EXTENTS  

NUMBER  

Number of deallocated extents  

FREE_REQUESTS  

NUMBER  

Number of requests to deallocate  

MAX_SIZE  

NUMBER  

Maximum number of extents ever used  

MAX_BLOCKS  

NUMBER  

Maximum number of blocks ever used  

MAX_USED_SIZE  

NUMBER  

Maximum number of extents used by all sorts  

MAX_USED_BLOCKS  

NUMBER  

Maximum number of blocks used by all sorts  

MAX_SORT_SIZE  

NUMBER  

Maximum number of extents used by an individual sort  

MAX_SORT_BLOCKS  

NUMBER  

Maximum number of blocks used by an individual sort  

V$SORT_USAGE

This view describes sort usage.

Column   Datatype   Description  

USER  

VARCHAR2(30)  

User who requested temporary space  

SESSION_ADDR  

RAW(4)  

Address of shared SQL cursor  

SESSION_NUM  

NUMBER  

Serial number of session  

SQLADDR  

RAW(4)  

Address of SQL statement  

SQLHASH  

NUMBER  

Hash value of SQL statement  

TABLESPACE  

VARCHAR2(31)  

Tablespace in which space is allocated  

CONTENTS  

VARCHAR2(9)  

Indicates whether tablespace is TEMPORARY/PERMANENT  

SEGFILE#  

NUMBER  

File number of initial extent  

SEGBLK#  

NUMBER  

Block number of the initial extent  

EXTENTS  

NUMBER  

Extents allocated to the sort  

BLOCKS  

NUMBER  

Extents in blocks allocated to the sort  

SEGRFNO#  

NUMBER  

Relative file number of initial extent  

V$SQL

This view lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.

Column   Datatype   Description  

SQL_TEXT  

VARCHAR2(1000)  

The first eighty characters of the SQL text for the current cursor  

SHARABLE_MEM  

NUMBER  

The amount of sharable memory, in bytes used by this child cursor  

PERSISTENT_MEM  

NUMBER  

The amount of persistent memory, in bytes used by this child cursor  

RUNTIME_MEM  

NUMBER  

The size of the ephemeral frame used by this child cursor  

SORTS  

NUMBER  

The number of sorts that was done for this child cursor  

LOADED_VERSIONS  

NUMBER  

1 if context heap is loaded, 0 otherwise  

OPEN_VERSIONS  

NUMBER  

1 if the child cursor is locked, 0 otherwise  

USERS_OPENING  

NUMBER  

The number of users executing the statement  

EXECUTIONS  

NUMBER  

The number of executions that took place on this object since it was brought into the library cache  

USERS_EXECUTING  

NUMBER  

The number of users executing the statement  

LOADS  

NUMBER  

The number of times the object was loaded or reloaded  

FIRST_LOAD_TIME  

VARCHAR2(19)  

The time stamp of the parent creation time  

INVALIDATIONS  

NUMBER  

The number of times this child cursor has been invalidated  

PARSE_CALLS  

NUMBER  

The number of parse calls for this child cursor  

DISK_READS  

NUMBER  

The number of disk reads for this child cursor  

BUFFER_GETS  

NUMBER  

The number of buffer gets for this child cursor  

ROWS_PROCESSED  

NUMBER  

The total number of rows the parsed SQL statement returns  

COMMAND_TYPE  

NUMBER  

The Oracle command type definition  

OPTIMIZER_MODE  

VARCHAR2(10)  

Mode under which the SQL statement is executed  

OPTIMIZER_COST  

NUMBER  

The cost of this query given by the optimizer  

PARSING_USER_ID  

NUMBER  

The user ID of the user who originally built this child cursor  

PARSING_SCHEMA_ID  

NUMBER  

The schema ID that was used to originally build this child cursor  

KEPT_VERSIONS  

NUMBER  

Indicates whether this child cursor has been marked to be kept pinned in cache using the DBMS_SHARED_POOL package  

ADDRESS  

RAW(4)  

The address of the handle to the parent for this cursor  

TYPE_CHK_HEAP  

RAW(4)  

The descriptor of the type check heap for this child cursor  

HASH_VALUE  

NUMBER  

The hash value of the parent statement in the library cache  

CHILD_NUMBER  

NUMBER  

The number of this child cursor  

MODULE  

VARCHAR2(64)  

Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION
_INFO.SET_MODULE  

MODULE_HASH  

NUMBER  

The hash value of the module that is named in the MODULE column  

ACTION  

VARCHAR2(64)  

Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION
_INFO.SET_ACTION  

ACTION_HASH  

NUMBER  

The hash value of the action that is named in the ACTION column  

SERIALIZABLE_ABORTS  

NUMBER  

The number of times the transaction fails to serialize, producing ORA-8177 errors, per cursor  

V$SQL_BIND_DATA

This view displays the actual bind data sent by the client for each distinct bind variable in each cursor owned by the session querying this view if the data is available in the server.

Column   Datatype   Description  

CURSOR_NUM  

NUMBER  

Cursor number for this bind  

POSITION  

NUMBER  

Bind position  

DATATYPE  

NUMBER  

Bind datatype  

SHARED_MAX_LEN  

NUMBER  

Shared maximum length for this bind from the shared cursor object associated with this bind  

PRIVATE_MAX_LEN  

NUMBER  

Private maximum length for this bind sent from the client  

ARRAY_SIZE  

NUMBER  

Maximum number of array elements (for array binds only)  

PRECISION  

NUMBER  

Precision (for numeric binds)  

SCALE  

NUMBER  

Scale (for numeric binds)  

SHARED_FLAG  

NUMBER  

Shared bind data flags  

SHARED_FLAG2  

NUMBER  

Shared bind data flags (continued)  

BUF_ADDRESS  

RAW(4)  

Bind buffer memory address  

BUF_LENGTH  

NUMBER  

Bind buffer length  

VAL_LENGTH  

NUMBER  

Actual bind value length  

BUF_FLAG  

NUMBER  

Bind buffer flags  

INDICATOR  

NUMBER  

Bind indicator  

VALUE  

VARCHAR2(4000)  

Contents of the bind buffer  

V$SQL_BIND_METADATA

This view displays bind metadata provided by the client for each distinct bind variable in each cursor owned by the session querying this view.

Column   Datatype   Description  

ADDRESS  

RAW(4)  

Memory address of the child cursor that owns this bind variable  

POSITION  

NUMBER  

Bind position  

DATATYPE  

NUMBER  

Bind datatype  

MAX_LENGTH  

NUMBER  

Maximum length of the bind value  

ARRAY_LEN  

NUMBER  

Maximum number of array elements (for array binds only)  

BIND_NAME  

VARCHAR2(30)  

Bind variable name (if used)  

V$SQL_CURSOR

This view displays debugging information for each cursor associated with the session querying this view.

Column   Datatype   Description  

CURNO  

NUMBER  

Cursor number  

FLAG  

NUMBER  

Flags set in the cursor  

STATUS  

VARCHAR2(9)  

Status of the cursor; that is, what state the cursor is in  

PARENT_HANDLE  

RAW(4)  

Pointer to the parent cursor handle  

PARENT_LOCK  

RAW(4)  

Pointer to the parent cursor lock  

CHILD_LOCK  

RAW(4)  

Pointer to the child cursor lock  

CHILD_PIN  

RAW(4)  

Pointer to the child cursor pin  

PERS_HEAP_MEM  

NUMBER  

Total amount of memory allocated from persistent heap for this cursor  

WORK_HEAP_MEM  

NUMBER  

Total amount of memory allocated from the work heap for this cursor  

BIND_VARS  

NUMBER  

Total number of bind positions in the query currently parsed into this cursor  

DEFINE_VARS  

NUMBER  

Total number of define variables in the query currently parsed into this cursor  

BIND_MEM_LOC  

VARCHAR2(64)  

Which memory heap the bind variables are stored in: either the UGA or the CGA  

INST_FLAG  

VARCHAR2(64)  

Instantiation object flags  

INST_FLAG2  

VARCHAR2(64)  

Instantiation object flags (continued)  

V$SQL_SHARED_MEMORY

This view displays information about the cursor shared memory snapshot. Each SQL statement stored in the shared pool has one or more child objects associated with it. Each child object has a number of parts, one of which is the context heap, which holds, among other things, the query plan.

Column   Datatype   Description  

SQL_TEXT  

VARCHAR2(1000)  

The SQL text of the shared cursor child object that this row is displaying information for.  

HASH_VALUE  

NUMBER  

The hash value of the above SQL text in the shared pool.  

HEAP_DESC  

RAW(4)  

The address of the descriptor for the context heap of the child cursor described in this row.  

STRUCTURE  

VARCHAR2(16)  

If the memory chunk described in this row was allocated using a comment of the form "X : Y", then this is the "X" part of the comment.  

FUNCTION  

VARCHAR2(16)  

Similar to the STRUCTURE column, this is the "Y" field of the comment.  

COMMENT  

VARCHAR2(16)  

This is the whole comment field that was supplied when this memory chunk was allocated.  

CHUNK_PTR  

RAW(4)  

This is the starting address of the allocated memory chunk.  

CHUNK_SIZE  

NUMBER  

The amount of memory allocated for this chunk.  

ALLOC_CLASS  

VARCHAR2(8)  

Class of memory that this chunk of memory belongs to. It will usually be either FREEABLE or PERMANENT.  

CHUNK_TYPE  

NUMBER  

An index into a table of callback functions that tell the server how to recreate this chunk of memory should it need to be LRU'd out of the shared pool.  

SUBHEAP_DESC  

RAW(4)  

If the parent heap of this context heap is itself a subheap, then this is the address of the descriptor of the parent heap.  

V$SQLAREA

This view lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

Column   Datatype   Description  

SQL_TEXT  

VARCHAR2(1000)  

The first eighty characters of the SQL text for the current cursor  

SHARABLE_MEM  

NUMBER  

The sum of all sharable memory, in bytes, of all the child cursors under this parent  

PERSISTENT_MEM  

NUMBER  

The sum of all persistent memory, in bytes, of all the child cursors under this parent  

RUNTIME_MEM  

NUMBER  

The sum of all the ephemeral frame sizes of all the children  

SORTS  

NUMBER  

The sum of the number of sorts that was done for all the children  

VERSION_COUNT  

NUMBER  

The number of children that are present in the cache under this parent  

LOADED_VERSIONS  

NUMBER  

The number of children that are present in the cache AND have their context heap (KGL heap 6) loaded  

OPEN_VERSIONS  

NUMBER  

The number of child cursors that are currently open under this current parent  

USERS_OPENING  

NUMBER  

The number of users that have any of the child cursors open  

EXECUTIONS  

NUMBER  

The total number of executions, totalled over all the children  

USERS_EXECUTING  

NUMBER  

The total number of users executing the statement over all children  

LOADS  

NUMBER  

The number of times the object was loaded or reloaded  

FIRST_LOAD_TIME  

VARCHAR2(19)  

The time stamp of the parent creation time  

INVALIDATIONS  

NUMBER  

The total number of invalidations over all the children  

PARSE_CALLS  

NUMBER  

The sum of all parse calls to all the child cursors under this parent  

DISK_READS  

NUMBER  

The sum of the number of disk reads over all child cursors  

BUFFER_GETS  

NUMBER  

The sum of buffer gets over all child cursors  

ROWS_PROCESSED  

NUMBER  

The total number of rows processed on behalf of this SQL statement  

COMMAND_TYPE  

NUMBER  

The Oracle command type definition  

OPTIMIZER_MODE  

VARCHAR2(10)  

Mode under which the SQL statement is executed  

PARSING_USER_ID  

NUMBER  

The user ID of the user that has parsed the very first cursor under this parent  

PARSING_SCHEMA_ID  

NUMBER  

The schema ID that was used to parse this child cursor  

KEPT_VERSIONS  

NUMBER  

The number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package  

ADDRESS  

RAW(4)  

The address of the handle to the parent for this cursor  

HASH_VALUE  

NUMBER  

The hash value of the parent statement in the library cache  

MODULE  

VARCHAR2(64)  

Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION
_INFO.SET_MODULE  

MODULE_HASH  

NUMBER  

The hash value of the module that is named in the MODULE column  

ACTION  

VARCHAR2(64)  

Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION
_INFO.SET_ACTION  

ACTION_HASH  

NUMBER  

The hash value of the action that is named in the ACTION column  

SERIALIZABLE_ABORTS  

NUMBER  

The number of times the transaction fails to serialize, producing ORA-8177 errors, totalled over all the children  

V$SQLTEXT

This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

Column   Datatype   Description  

ADDRESS  

RAW  

Used with HASH_VALUE to identify uniquely a cached cursor  

HASH_VALUE  

NUMBER  

Used with ADDRESS to identify uniquely a cached cursor  

PIECE  

NUMBER  

Number used to order the pieces of SQL text  

SQL_TEXT  

VARCHAR2  

A column containing one piece of the SQL text  

COMMAND_TYPE  

NUMBER  

Code for the type of SQL statement (SELECT, INSERT, etc.)  

V$SQLTEXT_WITH_NEWLINES

This view is identical to the V$SQLTEXT view except that, to improve legibility, V$SQLTEXT_WITH_NEWLINES does not replace newlines and tabs in the SQL statement with spaces. For more information, see "V$SQLTEXT" on page 3-104.

Column   Datatype   Description  

ADDRESS  

RAW  

Used with HASH_VALUE to identify uniquely a cached cursor  

HASH_VALUE  

NUMBER  

Used with ADDRESS to identify uniquely a cached cursor  

PIECE  

NUMBER  

Number used to order the pieces of SQL text  

SQL_TEXT  

VARCHAR2  

A column containing one piece of the SQL text  

COMMAND_TYPE  

NUMBER  

Code for the type of SQL statement (SELECT, INSERT, etc.)  

V$STATNAME

This view displays decoded statistic names for the statistics shown in the V$SESSTAT and V$SYSSTAT tables. For more information, see "V$SESSTAT" on page 3-91 and "V$SYSSTAT" on page 3-108.

Column   Datatype   Description  

STATISTIC#  

NUMBER  

Statistic number  

NAME  

VARCHAR2  

Statistic name. See also Table 3-13  

CLASS  

NUMBER  

Statistic class:
1 (User),
2 (Redo),
4 (Enqueue),
8 (Cache),
16 (OS),
32 (Parallel Server),
64 (SQL),
128 (Debug)  

Table 3-13 lists the generic Oracle Server statistics returned by V$STATNAME. For a complete description of each statistic, see Appendix C, "Statistics Descriptions".

Additional Information: On some platforms, the NAME and CLASS columns will contain additional operating system-specific statistics. See your operating system-specific Oracle documentation for more information about these statistics.

Table 3-13: V$SESSTAT and V$SYSSTAT Statistics Names

CPU used by this session  

CR blocks created  

Current blocks converted for CR  

DBWR Flush object call found no dirty buffers  

DBWR Flush object cross instance calls  

DBWR buffers scanned  

DBWR checkpoints  

DBWR cross instance writes  

DBWR free buffers found  

DBWR lru scans  

DBWR make free requests  

DBWR summed scan depth  

DBWR timeouts  

DDL statements parallelized  

DML statements parallelized  

PX local messages received  

PX local messages sent  

PX remote messages received  

PX remote messages sent  

SQL*Net roundtrips to/from client  

SQL*Net roundtrips to/from dblink  

Unnecessary process cleanup for SCN
batching  

background checkpoints completed  

background checkpoints started  

bytes received via SQL*Net from client  

bytes received via SQL*Net from client  

bytes sent via SQL*Net to client  

bytes sent via SQL*Net to dblink  

calls to get snapshot scn: kcmgss  

change write time  

cluster key scan block gets  

cluster key scans  

commit cleanout failures: block lost  

commit cleanout failures: buffer being
written  

commit cleanout failures: callback failure  

commit cleanout failures: cannot pin  

commit cleanout failures: hot backup in progress  

commit cleanout failures: write disabled  

commit cleanouts  

commit cleanouts successfully
completed  

consistent changes  

consistent gets  

cross instance CR read  

db block changes  

db block gets  

dirty buffers inspected  

enqueue conversions  

enqueue deadlocks  

enqueue releases  

enqueue requests  

enqueue timeouts  

enqueue waits  

exchange deadlocks  

execute count  

free buffer inspected  

free buffer requested  

global cache defers  

global cache freelist waits  

global cache hash latch waits  

global lock convert time  

global lock converts (async)  

global lock converts (non async)  

global lock get time  

global lock gets (async)  

global lock gets (non async)  

global lock release time  

global lock releases  

kcmccs called get current scn  

kcmccs read scn without going to DLM  

kcmccs waited for batching  

lock element waits  

logons cumulative  

logons current  

next scns gotten without going to DLM  

opened cursors cumulative  

opened cursors current  

opens of replaced files  

pens requiring cache replacement  

parse count (hard)  

parse count (soft)  

parse count (total)  

parse time cpu  

parse time elapsed  

physical reads  

physical writes  

queries parallelized  

recovery array read time  

recovery array reads  

recovery blocks read  

recursive calls  

recursive cpu usage  

redo entries  

redo entries linearized  

redo log space requests  

redo log space wait time  

redo log switch interrupts  

redo ordering marks  

redo size  

redo synch time  

redo sync writes  

redo wastage  

redo write time  

redo writer latching time  

redo writes  

remote instance undo block writes  

remote instance undo header writes  

remote instance undo requests  

serializable aborts  

session connect time  

session cursor cache count  

session cursor cache hits  

session logical reads  

session pga memory  

session pga memory max  

session stored procedure space  

session uga memory  

session uga memory max  

sorts (disk)  

sorts (memory)  

sorts (rows)  

summed dirty queue length  

table fetch by rowid  

table fetch continued row  

table scan blocks gotten  

table scan rows gotten  

table scans (cache partitions)  

table scans (direct read)  

table scans (long tables)  

table scans (rowid ranges)  

table scans (short tables)  

total file opens  

user calls  

user commits  

user rollbacks  

write requests  

 

V$SUBCACHE

This view displays information about the subordinate caches currently loaded into library cache memory. The view walks through the library cache, printing out a row for each loaded subordinate cache per library cache object.

Column   Datatype   Description  

OWNER_NAME  

VARCHAR2(64)  

Owner of object containing these cache entries  

NAME  

VARCHAR2(1000)  

Object Name  

TYPE  

NUMBER  

Object Type  

HEAP_NUM  

NUMBER  

Heap number containing this subordinate cache  

CACHE_ID  

NUMBER  

Subordinate cache ID  

CACHE_CNT  

NUMBER  

Number of entries for this cache in this object  

HEAP_SZ  

NUMBER  

Amount of extent space allocated to this heap  

HEAP_ALOC  

NUMBER  

Amount of extent space allocated from this heap  

HEAP_USED  

NUMBER  

Amount of space utilized in this heap  

V$SYSSTAT

This view lists system statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), see "V$STATNAME" on page 3-105.

Column   Datatype   Description  

STATISTIC#  

NUMBER  

Statistic number  

NAME  

VARCHAR2(64)  

Statistic name. See Table 3-13 on page 107.  

CLASS  

NUMBER  

Statistic class:
1 (User),
2 (Redo),
4 (Enqueue),
8 (Cache),
16 (OS),
32 (Parallel Server),
64 (SQL),
128 (Debug)  

VALUE  

NUMBER  

Statistic value  

V$SYSTEM_CURSOR_CACHE

This view displays similar information to the V$SESSION_CURSOR_CACHE view except that this information is system wide. For more information, see "V$SESSION_CURSOR_CACHE" on page 3-86.

Column   Datatype   Description  

OPENS  

NUMBER  

Cumulative total of cursor opens  

HITS  

NUMBER  

Cumulative total of cursor open hits  

HIT_RATIO  

NUMBER  

Ratio of the number of times you found an open cursor divided by the number of times you looked for a cursor  

V$SYSTEM_EVENT

This view contains information on total waits for an event. Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance. For more information, see"TIMED_STATISTICS" on page 1-113.

Column   Datatype   Description  

EVENT  

VARCHAR2(64)  

The name of the wait event  

TOTAL_WAITS  

NUMBER  

The total number of waits for this event  

TOTAL_TIMEOUTS  

NUMBER  

The total number of timeouts for this event  

TIME_WAITED  

NUMBER  

The total amount of time waited for this event, in hundredths of a second  

AVERAGE_WAIT  

NUMBER  

The average amount of time waited for this event, in hundredths of a second  

V$SYSTEM_PARAMETER

This view contains information on system parameters.

Column   Datatype   Description  

NUM  

NUMBER  

Parameter number  

NAME  

VARCHAR2(64)  

Parameter name  

TYPE  

NUMBER  

Parameter type; 1 = Boolean,
2 = string, 3 = integer  

VALUE  

VARCHAR2(512)  

Value assigned to the parameter  

ISDEFAULT  

VARCHAR2(9)  

Is the value assigned to the parameter the default  

ISSES_MODIFIABLE  

VARCHAR2(5)  

Whether the parameter can be modified by ALTER SESSION  

ISSYS_MODIFIABLE  

VARCHAR2(9)  

Whether the parameter can be modified by ALTER SYSTEM  

ISMODIFIED  

VARCHAR2(8)  

Indicates how the parameter was modified. If an ALTER SESSION was performed, the value will be MODIFIED. If an ALTER SYSTEM (which will cause all the currently logged in sessions' values to be modified) was performed the value will be SYS_MODIFIED.  

ISADJUSTED  

VARCHAR2(5)  

Indicates that the rdbms adjusted the input value to a more suitable value (e.g., the parameter value should be prime, but the user input a non-prime number, so the rdbms adjusted the value to the next prime number)  

DESCRIPTION  

VARCHAR2(64)  

Descriptive text about the parameter  

V$TABLESPACE

This view displays tablespace information from the controlfile.

Column   Datatype   Description  

TS#  

NUMBER  

Tablespace number  

NAME  

VARCHAR2 (30)  

Tablespace name  

V$THREAD

This view contains thread information from the control file.

Column   Datatype   Description  

THREAD#  

NUMBER  

Thread number  

STATUS  

VARCHAR2  

Thread status: OPEN, CLOSED  

ENABLED  

VARCHAR2  

Enabled status: DISABLED, (enabled) PRIVATE, or (enabled) PUBLIC  

ENABLE_CHANGE#  

NUMBER  

SCN at which thread was enabled  

ENABLE_TIME  

DATE  

Time of enable SCN  

DISABLE_CHANGE#  

NUMBER  

SCN at which thread was disabled  

DISABLE_TIME  

DATE  

Time of disable SCN  

GROUPS  

NUMBER  

Number of log groups assigned to this thread  

INSTANCE  

VARCHAR2  

Instance name, if available  

OPEN_TIME  

DATE  

Last time the thread was opened  

CURRENT_GROUP#  

NUMBER  

Current log group  

SEQUENCE#  

NUMBER  

Sequence number of current log  

CHECKPOINT
_CHANGE#  

NUMBER  

SCN at last checkpoint  

CHECKPOINT_TIME  

DATE  

Time of last checkpoint  

V$TIMER

This view lists the elapsed time in hundredths of seconds. Time is measured since the beginning of the epoch, which is operating system specific, and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days).

Column   Datatype   Description  

HSECS  

NUMBER  

Elapsed time in hundredths of a second  

V$TRANSACTION

This view lists the active transactions in the system.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of transaction state object  

XIDUSN  

NUMBER  

Undo segment number  

XIDSLOT  

NUMBER  

Slot number  

XIDSQN  

NUMBER  

Sequence number  

UBAFIL  

NUMBER  

Undo block address (UBA) filenum  

UBABLK  

NUMBER  

UBA block number  

UBASQN  

NUMBER  

UBA sequence number  

UBAREC  

NUMBER  

UBA record number  

STATUS  

VARCHAR2(16)  

Status  

START_TIME  

VARCHAR2(20)  

Start time (wall clock)  

START_SCNB  

NUMBER  

Start system change number (SCN) base  

START_SCNW  

NUMBER  

Start SCN wrap  

START_UEXT  

NUMBER  

Start extent number  

START_UBAFIL  

NUMBER  

Start UBA file number  

START_UBABLK  

NUMBER  

Start UBA block number  

START_UBASQN  

NUMBER  

Start UBA sequence number  

START_UBAREC  

NUMBER  

Start UBA record number  

SES_ADDR  

RAW(4)  

User session object address  

FLAG  

NUMBER  

Flag  

SPACE  

VARCHAR2(3)  

"Yes", if a space transaction  

RECURSIVE  

VARCHAR2(3)  

"Yes", if a recursive transaction  

NOUNDO  

VARCHAR2(3)  

"Yes" if a no undo transaction  

PTX  

VARCHAR 2(3)  

YES if parallel transaction, otherwise set to NO  

PRV_XIDUSN  

NUMBER  

Previous transaction undo segment number  

PRV_XIDSLT  

NUMBER  

Previous transaction slot number  

PRV_XIDSQN  

NUMBER  

Previous transaction sequence number  

PTX_XIDUSN  

NUMBER  

Rollback segment number of the parent XID  

PTX_XIDSLT  

NUMBER  

Slot number of the parent XID  

PTX_XIDSQN  

NUMBER  

Sequence number of the parent XID  

DSCN_B  

NUMBER  

Dependent SCN base  

DSCN_W  

NUMBER  

Dependent SCN wrap  

USED_UBLK  

NUMBER  

Number of undo blocks used  

USED_UREC  

NUMBER  

Number of undo records used  

LOG_IO  

NUMBER  

Logical I/O  

PHY_IO  

NUMBER  

Physical I/O  

CR_GET  

NUMBER  

Consistent gets  

CR_CHANGE  

NUMBER  

Consistent changes  

V$TRANSACTION_ENQUEUE

V$TRANSACTION_ENQUEUE displays locks owned by transaction state objects.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of lock state object  

KADDR  

RAW(4)  

Address of lock  

SID  

NUMBER  

Identifier for session holding or acquiring the lock  

TYPE  

VARCHAR2(2)  

Type of lock. TX = transaction enqueue.  

ID1  

NUMBER  

Lock identifier #1 (depends on type)  

ID2  

NUMBER  

Lock identifier #2 (depends on type)  

LMODE  

NUMBER  

Lock mode in which the session holds the lock:
0, None
1, Null (NULL)
2, Row-S (SS)
3, Row-X (SX)
4, Share (S)
5, S/Row-X (SSX)
6, Exclusive (X)  

REQUEST  

NUMBER  

Lock mode in which the process requests the lock:
0, None
1, Null (NULL)
2, Row-S (SS)
3, Row-X (SX)
4, Share (S)
5, S/Row-X (SSX)
6, Exclusive (X)  

CTIME  

NUMBER  

Time since current mode was granted  

BLOCK  

NUMBER  

The lock is blocking another lock  

V$TYPE_SIZE

This view lists the sizes of various database components for use in estimating data block capacity.

Column   Datatype   Description  

COMPONENT  

VARCHAR2  

Component name, such as segment or buffer header  

TYPE  

VARCHAR2  

Component type  

DESCRIPTION  

VARCHAR2  

Description of component  

TYPE_SIZE  

NUMBER  

Size of component  

V$VERSION

Version numbers of core library components in the Oracle Server. There is one row for each component.

Column   Datatype   Description  

BANNER  

VARCHAR2  

Component name and version number  

V$WAITSTAT

This view lists block contention statistics. This table is only updated when timed statistics are enabled.

Column   Datatype   Description  

CLASS  

VARCHAR2  

Class of block  

COUNT  

NUMBER  

Number of waits by this OPERATION for this CLASS of block  

TIME  

NUMBER  

Sum of all wait times for all the waits by this OPERATION for this CLASS of block  




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index