Oracle8 Reference Release 8.0 A58242-01 |
Library | Product | Contents | Index

# 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
- View Descriptions

## 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-86 and Oracle8 Parallel Server Concepts and Administration.

#### Restrictions on GV$ Views

GV$ views have the following restrictions:

- The value of the PARALLEL_MAX_SERVERS parameter must be greater than zero on all instances mounting the database.
- The OPS_ADMIN_GROUP parameter must be defined with at least one member for a query to successfully complete.

### 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 Administrator'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 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$AQ

This view describes statistics for the queues in the database.

Column | Datatype | Description
--- | --- | ---
QID | NUMBER | The unique queue identifier
WAITING | NUMBER | Number of messages in the queue in the state `WAITING'
READY | NUMBER | Number of messages in the queue in the state `READY'
EXPIRED | NUMBER | Number of messages in the queue the state `EXPIRED'
TOTAL_WAIT | NUMBER | Total wait time of all `READY' messages in the queue
AVERAGE_WAIT | NUMBER | Average wait time of `READY' messages in the queue

### V$ARCHIVE

This view contains information on redo log files in need of archiving. 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-60, "LOG_ARCHIVE_DUPLEX_DEST" on page 1-61, and "LOG_ARCHIVE_MIN_SUCCEED_DEST" on page 1-62

### 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 and 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 and 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.
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 and 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$CURRENT_BUCKET

This view displays information useful for predicting the number of additional cache misses that would occur if the number of buffers in the cache were reduced.

Column | Datatype | Description
--- | --- | ---
COUNT | NUMBER | The count

### 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-1.
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-22.

Column | Datatype | Description
--- | --- | ---
FILE# | NUMBER | File identifier
NAME | VARCHAR2 | Name of file

### V$DBLINK

This view describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. 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-100.
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_LOCKS

This is a Parallel Server view. V$DLM_LOCKS lists information of all locks currently known to lock manager that are being blocked or blocking others.

Column | Datatype | Description
--- | --- | ---
LOCKP | RAW(4) | Lock Pointer
GRANT_LEVEL | VARCHAR2(9) | Granted level of the lock
REQUEST_LEVEL | VARCHAR2(9) | Requested level of the lock
RESOURCE_NAME | VARCHAR2(64) | Resource name for the lock
PID | NUMBER | Process identifier which holds the lock
TRANSACTION_ID0 | NUMBER | Lower 4 bytes of the transaction identifier where the lock belongs to
TRANSACTION_ID1 | NUMBER | Upper 4 bytes of the transaction identifier where the lock belongs to
GROUP_ID | NUMBER | Group identifier for the lock
OPEN_OPT_DEADLOCK | NUMBER | 1 if DEADLOCK open option is set, otherwise 0
OPEN_OPT_PERSISTENT | NUMBER | 1 if PERSISTENT open option is set, otherwise 0
OPEN_OPT_PROCESS _OWNED | NUMBER | 1 if PROCESS_OWNED open option is set, otherwise 0
OPEN_OPT_NO_XID | NUMBER | 1 if NO_XID open option is set, otherwise 0
CONVERT_OPT _GETVALUE | NUMBER | 1 if GETVALUE convert option is set, otherwise 0
CONVERT_OPT _PUTVALUE | NUMBER | 1 if PUTVALUE convert option is set, otherwise 0
CONVERT_OPT _NOVALUE | NUMBER | 1 if NOVALUE convert option is set, otherwise 0
CONVERT_OPT _DUBVALUE | NUMBER | 1 if DUBVALUE convert option is set, otherwise 0
CONVERT_OPT _NOQUEUE | NUMBER | 1 if NOQUEUE convert option is set, otherwise 0
CONVERT_OPT_EXPRESS | NUMBER | 1 if EXPRESS convert option is set, otherwise 0
CONVERT_OPT_ NODEADLOCKWAIT | NUMBER | 1 if NODEADLOCKWAIT convert option is set, otherwise 0
CONVERT_OPT_ NODEADLOCKBLOCK | NUMBER | 1 if NODEADLOCKBLOCK convert option is set, otherwise 0
WHICH_QUEUE | NUMBER | Which queue the lock is currently located. 0 for NULL queue 1 for GRANTED queue 2 for CONVERT queue
LOCKSTATE | VARCHAR2(64) | State of lock as owner sees it
AST_EVENT0 | NUMBER | Last AST event
OWNER_NODE | NUMBER | Node identifier
BLOCKED | NUMBER | 1 if this lock request is blocked by others, otherwise 0
BLOCKER | NUMBER | 1 if this lock is blocking others, otherwise 0

### 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-4, "Values for the LATCH_TYPE column".
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.
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 and 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 hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE
WRITETIM | NUMBER | Time (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE
AVGIOTIM | NUMBER | The average time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE
LSTIOTIM | NUMBER | The time (in hundredths of a second) spent doing the last I/O, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE
MINIOTIM | NUMBER | The minimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE
MAXIOWTM | NUMBER | The maximum time (in hundredths of a second) spent doing a single write, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE
MAXIORTM | NUMBER | The maximum time (in hundredths of a second) spent doing a single read, 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. Some V$ tables refer to real tables and are therefore not listed.

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-51.
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

### 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
PTNLOADED | NUMBER | Number of records loaded PTN

### 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 and 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 and 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 and 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-58.

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 your Trusted Oracle documentation.

### 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-77.

### 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 NLS_NCHAR_CHARACTERSET
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: LANGUAGE SORT TERRITORY 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-77 and "V$SQL" on page 3-95.

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-22.

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-27)
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.
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 and 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-6
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
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 STATISTICS 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$RECENT_BUCKET

This view displays information useful for estimating the performance of a large cache.

Column | Datatype | Description
--- | --- | ---
COUNT | NUMBER | The count

### 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 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-58.

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-61.

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

### V$RECOVERY_PROGRESS

V$RECOVERY_PROGRESS can be used to track database recovery operations to ensure that they are not stalled, and also to estimate the time required to complete the operation in progress.

V$RECOVERY_PROGRESS is a subview of V$SESSION_LONGOPS.

Column | Datatype | Description
--- | --- | ---
TYPE | VARCHAR2(64) | The type of recovery operation being performed
ITEM | VARCHAR2(11) | The item being measured
SOFAR | NUMBER | The amount of work done so far
TOTAL | NUMBER | The total amount of work expected

For further information, see The Oracle8 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 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-3 and Table 3-4
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-40.
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-56.
LM_RESOURCES | LM_RESS For more information on this parameter see "LM_RESS" on page 1-57.
LM_LOCKS | LM_LOCKS For more information on this parameter see "LM_LOCKS" on page 1-55.
MTS_MAX_SERVERS | MTS_MAX_SERVERS For more information on this parameter see "MTS_MAX_SERVERS" on page 1-74.
PARALLEL_SLAVES | PARALLEL_MAX_SERVERS For more information on this parameter, see "PARALLEL_MAX_SERVERS" on page 1-94.
PROCESSES | PROCESSES For more information on this parameter, see "PROCESSES" on page 1-100.
ROLLBACK_SEGMENTS | MAX_ROLLBACK_SEGMENTS For more information on this parameter, see "MAX_ROLLBACK_SEGMENTS" on page 1-69.
SESSIONS | SESSIONS For more information on this parameter, see "SESSIONS" on page 1-110.
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-122.

### V$ROLLNAME

This view lists the names of all online rollback segments. It 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-8
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. For operations using Parallel Slaves, interpret this value as a 4Byte value. The low-order 2Bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator.
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.
LOGON_TIME | DATE | Time of logon.
LAST_CALL_ET | NUMBER | The last call
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-122.

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
MAX_WAIT | NUMBER | The maximum time (in hundredths of a second) waited for this event by this session

### 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:

- hash cluster creations
- backup operations
- recovery operations

  Column | Datatype | Description
  --- | --- | ---
  SID | NUMBER | Session identifier
  SERIAL# | NUMBER | Session serial number
  UPDATE_COUNT | NUMBER | The updated count
  COMPNAM | VARCHAR2(30) | The component name
  OBJID | NUMBER | If present, displays the dictionary object ID on which the operation is being done
  CONTEXT | NUMBER | A context specific to the component
  STEPID | NUMBER | A number assigned by the component for the execution step
  MSG | VARCHAR2(512) | Text describing work done so far and total anticipated work on an object
  STEPSOFAR | NUMBER | Units of work done so far in the step
  STEPTOTAL | NUMBER | Total amount of work to be done in the step
  SOFAR | NUMBER | Total units of work done so far
  TOTALWORK | NUMBER | Total amount of work to be done. Zero if unknown
  APPLICATION _DATA_1 | NUMBER | This column may contain additional data relating to the long-running operation described by this row. The contents of this row vary depending on the type of long-running operation.
  APPLICATION _DATA_2 | NUMBER | This column may contain additional data relating to the long-running operation described by this row. The contents of this row vary depending on the type of long-running operation.
  APPLICATION _DATA_3 | NUMBER | This column may contain additional data relating to the long-running operation described by this row. The contents of this row vary depending on the type of long-running operation.
  START_TIME | DATE | The starting time
  CURRENT_TIME | DATE | The current time
  ELAPSED _SECONDS | NUMBER | The number of elapsed seconds

### 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:

- P1RAW, P2RAW, and P3RAW display the same values as the P1, P2, and P3 columns, except that the numbers are displayed in hexadecimal.
- The WAIT_TIME column contains a value of -2 on 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 the TIMED_STATISTICS parameter to TRUE. Remember that doing this has a small negative effect on system performance. For more information, see "TIMED_STATISTICS" on page 1-122.

  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.

- The STATE column interprets the value of WAIT_TIME and describes the state of the current or most recent wait.

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-103.

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-112.

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. The view is only updated when the tablespace is of the TEMPORARY type.

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-102.

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-89 and "V$SYSSTAT" on page 3-107.

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".

##### 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 |

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.

### 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-103.

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-84.

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-122.

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
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index