Oracle8i Reference
Release 8.1.5

A67790-01

Library

Product

Contents

Index

Prev Next

3
Dynamic Performance (V$) Views

This chapter describes the dynamic performance views These views are typically referred to as V$ views.

The following topics are included in this chapter:

Dynamic Performance Views

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

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

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

V$ Views

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

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

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

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

GV$ Views

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

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

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

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

Restrictions on GV$ Views

GV$ views have the following restrictions:

Access to the Dynamic Performance Tables

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

For more information, see Oracle Enterprise Manager Administrator's Guide.

View Descriptions

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

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

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  

DEST_ID  

NUMBER  

ID (1-5)  

STATUS  

VARCHAR2(9)  

Status: VALID: Initialized and available; INACTIVE: No destination information; DEFERRED: Manually disabled by the user; ERROR: Error during open or copy; DISABLED: Disabled after error; BAD PARAM: Parameter has errors  

BINDING  

VARCHAR2(9)  

Requirement for success: MANDATORY- must succeed or OPTIONAL - need not succeed (depends on LOG_ARCHIVE_MIS_SUCCEED_DEST)  

NAME_SPACE  

VARCHAR2(7)  

Definition scope: SYSTEM-System definition or SESSION-Session definition  

TARGET  

VARCHAR2(7)  

Target: PRIMARY-Copy to primary or STANDBY-Copy to standby  

REOPEN_SECS  

NUMBER  

Retry time in seconds (after error)  

DESTINATION  

VARCHAR2(256)  

Destination text string (translated primary location or standby service name)  

FAIL_DATE  

DATE  

Date and time of any last error  

FAIL_SEQUENCE  

NUMBER  

Any log sequence number at last error  

FAIL_BLOCK  

NUMBER  

Any block number at last error  

ERROR  

VARCHAR2(256)  

Text of any last error  

For more information on archived log destinations, see "LOG_ARCHIVE_DEST", "LOG_ARCHIVE_DUPLEX_DEST", and "LOG_ARCHIVE_DEST_n" "LOG_ARCHIVE_DEST_STATE_n", "STANDBY_ARCHIVE_DEST", "LOG_ARCHIVE_MIN_SUCCEED_DEST".

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  

ARCHIVED  

VARCHAR2(3)  

YES/NO  

DELETED  

VARCHAR2(3)  

YES/NO  

COMPLETION_TIME  

DATE  

Time when the archiving completed  

V$ARCHIVE_PROCESSES

This view provides information about the state of the various ARCH processes for the instance.

Column   Datatype   Description  

PROCESS  

NUMBER  

The identifier for the ARCH process for the instance, numbered from 0-9  

STATUS  

VARCHAR2(10)  

The status of the ARCH process, displayed as a keyword. Possible values are: STOPPED, SCHEDULED, STARTING, ACTIVE, STOPPING, and TERMINATED.  

LOG_SEQUENCE  

NUMBER  

This is the online redo log sequence number currently being archived, if STATE="BUSY"  

STATE  

VARCHAR2(4)  

This is the current state of the ARCH process, displayed as a keyword. Possible keywords are: IDLE or BUSY  

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_ASYNC_IO

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  

SID  

NUMBER  

The Oracle SID of the session doing the backup or restore  

SERIAL  

NUMBER  

The use count for the SID doing the backup or restore  

USE_COUNT  

NUMBER  

A counter that can be used to identify rows from different backup sets  

DEVICE_TYPE  

VARCHAR2(17)  

The device type where the file is located  

TYPE  

VARCHAR2(9)  

INPUT; OUTPUT; or AGGREGATE  

STATUS  

VARCHAR2(11)  

NOT STARTED; IN PROGRESS; or FINISHED  

FILENAME  

VARCHAR2(513)  

The name of the backup file being read or written  

SET_COUNT  

NUMBER  

The set_count of the backup set being read or written  

SET_STAMP  

NUMBER  

The set_stamp of the backup set being read or written  

BUFFER_SIZE  

NUMBER  

The size of the buffers being used to read/write this file, in bytes  

BUFFER_COUNT  

NUMBER  

The number of buffers being used to read/write this file  

TOTAL_BYTES  

NUMBER  

The total number of bytes that will be read or written for this file, if known. If not known, this column will be null  

OPEN_TIME  

DATE  

The time this file was opened. If TYPE='AGGREGATE', then this is the time that the first file in the aggregate was opened  

CLOSE_TIME  

DATE  

The time this file was closed. If TYPE='AGGREGATE', then this is the time that the last file in the aggregate was closed  

ELAPSED_TIME  

NUMBER  

The time, in 100ths of seconds, that the file was open  

MAXOPENFILES  

NUMBER  

The number of concurrently open DISK files. This value is only present in rows where TYPE='AGGREGATE'  

BYTES  

NUMBER  

The number of bytes read or written so far  

EFFECTIVE_BYTES_PER_SECOND  

NUMBER  

The I/O rate that was achieved with this device during this backup  

IO_COUNT  

NUMBER  

The number of I/Os that were performed to this file  

READY  

NUMBER  

The number of asynchronous requests for which a buffer was immediately ready for use  

SHORT_WAITS  

NUMBER  

The number of times that a buffer was not immediately available, but a buffer became available after doing a non-blocking poll for I/O completion  

SHORT_WAIT_TIME
_TOTAL  

NUMBER  

The total time, in 100ths of seconds, taken by non-blocking polls for I/O completion  

SHORT_WAIT_TIME
_MAX  

NUMBER  

The maximum time taken for a non-blocking poll for I/O completion, in 100ths of seconds  

LONG_WAITS  

NUMBER  

The number of times that a buffer was not immediately available, and only became available after a blocking wait was issued  

LONG_WAIT_TIME
_TOTAL  

NUMBER  

The total time, in 100ths of seconds, taken by blocking waits for I/O completion  

LONG_WAIT_TIME
_MAX  

NUMBER  

The maximum time taken for a blocking wait for I/O completion, in 100ths of seconds  

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)  

COPY#  

NUMBER  

Indicates the copy number for backup pieces created with duplex enabled. 1 if the backup piece is not duplexed  

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  

MEDIA_POOL  

NUMBER  

The media pool in which the copy resides. This is the same value that was entered in the POOL operand of the Recovery Manager BACKUP command  

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 `L'. If this is a datafile full backup, the value is `D'. If this is an incremental backup, the value is `I'  

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  

START_TIME  

DATE  

The starting time  

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$BACKUP_SYNC_IO

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  

SID  

NUMBER  

The Oracle SID of the session doing the backup or restore  

SERIAL  

NUMBER  

The use count for the SID doing the backup or restore  

USE_COUNT  

NUMBER  

A counter that can be used to identify rows from different backup sets  

DEVICE_TYPE  

VARCHAR2(17)  

The device type where the file is located  

TYPE  

VARCHAR2(9)  

INPUT; OUTPUT; or AGGREGATE  

STATUS  

VARCHAR2(11)  

NOT STARTED; IN PROGRESS; or FINISHED  

FILENAME  

VARCHAR2(513)  

The name of the backup file being read or written  

SET_COUNT  

NUMBER  

The set_count of the backup set being read or written  

SET_STAMP  

NUMBER  

The set_stamp of the backup set being read or written  

BUFFER_SIZE  

NUMBER  

The size of the buffers being used to read/write this file, in bytes  

BUFFER_COUNT  

NUMBER  

The number of buffers being used to read/write this file  

TOTAL_BYTES  

NUMBER  

The total number of bytes that will be read or written for this file, if known. If not known, this column will be null  

OPEN_TIME  

DATE  

The time this file was opened. If TYPE='AGGREGATE', then this is the time that the first file in the aggregate was opened  

CLOSE_TIME  

DATE  

The time this file was closed. If TYPE='AGGREGATE', then this is the time that the last file in the aggregate was closed  

ELAPSED_TIME  

NUMBER  

The time, in 100ths of seconds, that the file was open  

MAXOPENFILES  

NUMBER  

The number of concurrently open DISK files. This value is only present in rows where TYPE='AGGREGATE'  

BYTES  

NUMBER  

The number of bytes read or written so far  

EFFECTIVE_BYTES_PER_SECOND  

NUMBER  

The I/O rate that was achieved with this device during this backup  

IO_COUNT  

NUMBER  

The number of I/Os that were performed to this file  

IO_TIME_TOTAL  

NUMBER  

The total time, in 100ths of seconds, taken to do I/O for this file  

IO_TIME_MAX  

NUMBER  

The maximum time taken for a single I/O request  

DISCRETE_BYTES_PER_SECOND  

NUMBER  

The average transfer rate for this file  

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  

CLASS#  

NUMBER  

The class 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  

TS#  

NUMBER  

Tablespace number of block  

For more information, see Oracle8i 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".

Column   Datatype   Description  

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  

BUFFERS  

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$BUFFER_POOL_STATISTICS

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

Column   Datatype   Description  

ID  

NUMBER  

Buffer pool ID number  

NAME  

VARCHAR2(20)  

Buffer pool name  

SET_MSIZE  

NUMBER  

Buffer pool maximum set size  

CNUM_REPL  

NUMBER  

Number of buffers on replacement list  

CNUM_WRITE  

NUMBER  

Number of buffers on write list  

CNUM_SET  

NUMBER  

Number of buffers in set  

BUF_GOT  

NUMBER  

Number of buffers gotten by the set  

SUM_WRITE  

NUMBER  

Number of buffers written by the set  

SUM_SCAN  

NUMBER  

Number of buffers scanned in the set  

FREE_BUFFER_WAIT  

NUMBER  

Free buffer wait statistic  

WRITE_COMPLETE_WAIT  

NUMBER  

Write complete wait statistic  

BUFFER_BUSY_WAIT  

NUMBER  

Buffer busy wait statistic  

FREE_BUFFER_INSPECTED  

NUMBER  

Free buffer inspected statistic  

DIRTY_BUFFERS_INSPECTED  

NUMBER  

Dirty buffers inspected statistic  

DB_BLOCK_CHANGE  

NUMBER  

Database blocks changed statistic  

DB_BLOCK_GETS  

NUMBER  

Database blocks gotten statistic  

CONSISTENT_GETS  

NUMBER  

Consistent gets statistic  

PHYSICAL_READS  

NUMBER  

Physical reads statistic  

PHYSICAL_WRITES  

NUMBER  

Physical writes statistic  

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  

CLASS#  

NUMBER  

Class 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  

FORCED_READS  

NUMBER  

The forced reads  

FORCED_WRITES  

NUMBER  

The forced writes  

NAME  

VARCHAR2(30)  

Name of the database object containing the block  

PARTITION_NAME  

VARCHAR2(30)  

The name of the partition; 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 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  

For more information, see Oracle8i 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".

For more information, see Oracle8i 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(16)  

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(16)  

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), 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  

MESSAGE2  

NUMBER  

Size in bytes of the messages in the third message buffer  

MESSAGE3  

NUMBER  

Size in bytes of the messages in the fourth 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  

PRESENTATION  

VARCHAR2(16)  

The presentation protocol used by the client and server  

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$CONTEXT

This view lists set attributes in the current session.

Column   Datatype   Description  

NAMESPACE  

VARCHAR2(30)  

The name of namespace  

ATTRIBUTE  

VARCHAR2(30)  

The name of attribute  

VALUE  

VARCHAR2(64)  

The value of attribute  

V$CONTROLFILE

This view lists the names of the control files.

Column   Datatype   Description  

STATUS  

VARCHAR2(7)  

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

NAME  

VARCHAR2(257)  

The name of the control file  

V$CONTROLFILE_RECORD_SECTION

This view displays information about the controlfile record sections.

Column   Datatype   Description  

TYPE  

VARCHAR2(17)  

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

RECORD_SIZE  

NUMBER  

Record size in bytes  

RECORDS_TOTAL  

NUMBER  

Number of records allocated for the section  

RECORDS_USED  

NUMBER  

Number of records used in the section  

FIRST_INDEX  

NUMBER  

Index (position) of the first record  

LAST_INDEX  

NUMBER  

Index of the last record  

LAST_RECID  

NUMBER  

Record ID of the last record  

V$COPY_CORRUPTION

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

Column   Datatype   Description  

RECID  

NUMBER  

Copy corruption record ID  

STAMP  

NUMBER  

Copy corruption record stamp  

COPY_RECID  

NUMBER  

Datafile copy record ID  

COPY_STAMP  

NUMBER  

Datafile copy record stamp  

FILE#  

NUMBER  

Datafile number  

BLOCK#  

NUMBER  

First block of the corrupted range  

BLOCKS  

NUMBER  

Number of contiguous blocks in the corrupted range  

CORRUPTION_CHANGE#  

NUMBER  

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

MARKED_CORRUPT  

VARCHAR2(3)  

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

V$DATABASE

This view contains database information from the control file.

Column   Datatype   Description  

DBID  

NUMBER  

The database ID  

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  

PRIOR_RESETLOGS_CHANGE#  

NUMBER  

Change# at prior resetlogs  

PRIOR_RESETLOGS_TIME  

DATE  

Timestamp of prior 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  

VERSION_TIME  

DATE  

The version time  

OPEN_MODE  

VARCHAR2(10)  

Open mode information  

V$DATAFILE

This view contains datafile information from the control file. See also the "V$DATAFILE_HEADER" 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  

PLUGGED_IN  

NUMBER  

Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read-write, 0 if not.  

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  

INCREMENTAL_LEVEL  

NUMBER  

The incremental level  

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

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

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(1000)  

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

TYPE  

VARCHAR2(7)  

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 address of this dispatcher.  

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  

CONF_INDX  

NUMBER  

Zero-based index of the MTS_DISPATCHERS configuration used by this dispatcher  

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_ALL_LOCKS

This is a Parallel Server view. V$DLM_ALL_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_NAME1  

VARCHAR2(30)  

Resource name for the lock  

RESOURCE_NAME2  

VARCHAR2(30)  

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 the lock as the 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_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 is obsolete. See "V$LATCH" for statistics about DLM latch performance.

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_NAME1  

VARCHAR2(30)  

Resource name for the lock  

RESOURCE_NAME2  

VARCHAR2(30)  

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$DLM_RESS

V$DLM_RESS is a Parallel Server view. It displays information of all resources currently known to the lock manager.

Column   Datatype   Description  

RESP  

RAW(4)  

Resource pointer  

RESOURCE_NAME  

VARCHAR2(30)  

Resource name in hexadecimal for the lock  

ON_CONVERT_Q  

NUMBER  

1 if on convert queue, 0 otherwise  

ON_GRANT_Q  

NUMBER  

1 if on granted queue, 0 otherwise  

PERSISTENT_RES  

NUMBER  

1 if it is a persistent resource, 0 otherwise  

RDOMAIN_NAME  

VARCHAR2(25)  

Recovery domain name  

RDOMAINP  

RAW(4)  

Recovery domain pointer  

MASTER_NODE  

NUMBER  

Master node ID  

NEXT_CVT_LEVEL  

VARCHAR2(9)  

Next lock level to convert on global convert queue  

VALUE_BLK_STATE  

VARCHAR2(32)  

State of the value block  

VALUE_BLK  

VARCHAR2(64)  

First 64 bytes of the value block  

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

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.  

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 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$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_LOCKS" 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_LOCKS" and also Oracle8i Parallel Server Concepts and Administration.

V$FAST_START_SERVERS

V$FAST_START_SERVERS provides information about all the recovery slaves performing parallel transaction recovery.

Column   Datatype   Description  

STATE  

VARCHAR2(11)  

State of the server; IDLE or RECOVERING  

UNDOBLOCKSDONE  

NUMBER  

The percentage of the assigned work done so far  

PID  

NUMBER  

The process ID  

For further information, see Oracle8i Backup and Recovery Guide.

V$FAST_START_TRANSACTIONS

V$FAST_START_TRANSACTIONS contains information about the progress of the transactions that Oracle is recovering.

Column   Datatype   Description  

USN  

NUMBER  

The undo segment number of the transaction  

SLT  

NUMBER  

The slot within the rollback segment  

SEQ  

NUMBER  

The incarnation number of the slot  

STATE  

VARCHAR2(16)  

The state of the transaction may be TO BE RECOVERED, RECOVERED, or RECOVERING  

UNDOBLOCKSDONE  

NUMBER  

The number of undo blocks completed on this transaction  

UNDOBLOCKSTOTAL  

NUMBER  

The total number of undo blocks that need recovery  

PID  

NUMBER  

The ID of the current server it has been assigned to  

CPUTIME  

NUMBER  

The time for which recovery has progressed, in seconds  

PARENTUSN  

NUMBER  

The undo segment number of the parent transaction in PDML  

PARENTSLT  

NUMBER  

The slot of the parent transaction in PDML  

PARENTSEQ  

NUMBER  

The sequence number of the parent transaction in PDML  

For further information, see Oracle8i Backup and Recovery Guide.

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  

SS_2_RLS  

NUMBER  

Number of pcm locks sslocks released. 0 in Oracle 8.1  

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 reuse block range cross instance call for this file  

RBR_FORCED_WRITE  

NUMBER  

Number of blocks written due to reuse 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 reuse 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  

OP_2_SS  

NUMBER  

Number of pcm locks ss locks opened. 0 in Oracle 8.1  

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 (for example, V$ROLLNAME) 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_BLOCKED_LOCKS

This view displays global blocked locks.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of lock state object (raw)  

KADDR  

RAW(4)  

Address of lock (raw)  

SID  

NUMBER  

Identifier of session holding the lock (number)  

TYPE  

VARCHAR2(2)  

Resource type (char)  

ID1  

NUMBER  

Resource identifier #1 (number)  

ID2  

NUMBER  

Resource identifier #2 (number)  

LMODE  

NUMBER  

Lock mode held (number)  

REQUEST  

NUMBER  

Lock mode requested (number)  

CTIME  

NUMBER  

Time since current mode was granted  

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$HS_AGENT

This view identifies the set of HS agents currently running on a given host, using one row per agent process.

Column   Datatype   Description  

AGENT_ID  

NUMBER  

Net8 session identifier used for connections to agent (listerner.ora SID)  

MACHINE  

VARCHAR2(64)  

Operating system machine name  

PROCESS  

VARCHAR2(9)  

Operating system process identifier of agent  

PROGRAM  

VARCHAR2(48)  

Program name of agent  

OSUSER  

VARCHAR2(30)  

Operating system user  

STARTTIME  

DATE  

The starting time  

AGENT_TYPE  

NUMBER  

Type of agent  

FDS_CLASS_ID  

NUMBER  

The ID of the Foreign Data Store class  

FDS_INST_ID  

NUMBER  

The instance name of the Foreign Data Store  

V$HS_SESSION

This view identifies the set of HS sessions currently open for the Oracle Server.

Column   Datatype   Description  

HS_SESSION_ID  

NUMBER  

Unique HS session identifier  

AGENT_ID  

NUMBER  

Foreign key to V$HS_AGENT  

SID  

NUMBER  

User session identifier (foreign key to V$SESSION)  

DB_LINK  

VARCHAR2(128)  

Server database link name used to access the agent NULL means that no database link is used (eg, when using external procedures)  

DB_LINK_OWNER  

NUMBER  

Owner of the database link in DB_LINK  

STARTTIME  

DATE  

Time the connection was initiated  

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"  

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  

DATABASE_STATUS  

VARCHAR2(17)  

The status of the database  

V$INSTANCE_RECOVERY

This view is used to monitor the mechanisms that implement the user-specifiable limit on recovery reads.

Column   Datatype   Description  

RECOVERY_EXTIMATED_IOS  

NUMBER  

The estimated number of blocks that would be processed during recovery, based on the in-memory value of the Fast-Start checkpoint.  

ACTUAL_REDO_BLOCKS  

NUMBER  

The current actual number of redo blocks required for recovery.  

TARGET_REDO_BLOCKS  

NUMBER  

The current target number of redo blocks that must be processed for recovery. It will be the minimum of the following four columns.  

LOG_FILE_SIZE_REDO_BLKS  

NUMBER  

Maximum number of redo blocks required to guarantee log switch does not occur before checkpoint completes.  

LOG_CHKPT_TIMEOUT_REDO_BLKS  

NUMBER  

Number of redo blocks that need to be processed during recovery to satisfy LOG_CHECKPOINT_TIMEOUT.  

LOG_CHKPT_INTERVAL_REDO_BLKS  

NUMBER  

Number of redo blocks that need to be processed during recovery to satisfy LOG_CHECKPOINT_INTERVAL.  

FAST_START_IO_TARGET_REDO_BLKS  

NUMBER  

Number of redo blocks that need to be processed during recovery to satisfy FAST_START_IO_TARGET.  

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

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

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  

WTR_SLP_COUNT  

NUMBER  

 

LONGHOLD_COUNT  

NUMBER  

 

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

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-4, "Values for the TYPE column: User Types" and Table 3-5, "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-4 are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.

Table 3-4 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-5 are held for extremely short periods of time.

Table 3-5 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 Oracle8i 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 Oracle8i 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 Oracle8i 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-6.  

FIRST_CHANGE#  

NUMBER  

Lowest SCN in the log  

FIRST_TIME  

DATE  

Time of first SCN in the log  

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

Table 3-6 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".

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$LOGMNR_CONTENTS

This view contains log history information.

Column   Datatype   Description  

SCN  

NUMBER(15)  

The system change number  

TIMESTAMP  

DATE  

The timestamp  

THREAD#  

NUMBER  

The thread number  

LOG_ID  

NUMBER  

The log ID  

XIDUSN  

NUMBER  

The transaction ID undo segment number  

XIDSLOT  

NUMBER  

The transaction ID slot number  

XIDSQN  

NUMBER  

The transaction ID log sequence number  

RBASQN  

NUMBER  

The RBA log sequence number  

RBABLK  

NUMBER  

The RBA block number  

RBABYTE  

NUMBER  

The RBA byte offset  

UBAFIL  

NUMBER  

The UBA file number  

UBABLK  

NUMBER  

The UBA block number  

UBAREC  

NUMBER  

The UBA record index  

UBASQN  

NUMBER  

The UBA undo block sequence number  

ABS_FILE#  

NUMBER  

The data block absolute file number  

REL_FILE#  

NUMBER  

The data block relative file number  

DATA_BLK#  

NUMBER  

The data block number  

DATA_OBJ#  

NUMBER  

The data block object number  

DATA_DOBJ#  

NUMBER  

The data block data object number  

SEG_OWNER  

VARCHAR2(30)  

The owner name of the segment  

SEG_NAME  

VARCHAR2(81)  

The segment name  

SEG_TYPE  

NUMBER  

The segment type  

TABLE_SPACE_NAME  

VARCHAR2(30)  

The tablespace name of segment  

ROW_ID  

VARCHAR2(18)  

The row ID  

SESSION#  

NUMBER  

The session number  

SERIAL#  

NUMBER  

The serial number  

USER_NAME  

VARCHAR2(30)  

The user name  

SESSION_INFO  

VARCHAR2(4000)  

Session information  

ROLLBACK  

NUMBER  

The rollback request  

OPERATION  

VARCHAR2(30)  

The operation  

SQL_REDO  

VARCHAR2(4000)  

SQL redo  

SQL_UNDO  

VARCHAR2(4000)  

SQL undo  

RS_ID  

VARCHAR2(30)  

Record set ID  

SSN  

NUMBER  

SQL sequence number  

CSF  

NUMBER  

Continuation SQL flag  

INFO  

VARCHAR2(32)  

Informational message  

STATUS  

VARCHAR2 (16)  

The status  

V$LOGMNR_DICTIONARY

This view contains log history information.

Column   Datatype   Description  

TIMESTAMP  

DATE  

The date the dictionary was created  

DB_ID  

NUMBER  

The database ID  

DB_NAME  

VARCHAR2(8)  

The name of the database  

FILENAME  

VARCHAR2(513)  

The dictionary filename  

DICTIONARY_SCN  

NUMBER  

The system change number when the dictionary was created  

RESET_SCN  

NUMBER  

The reset log SCN when the dictionary was created  

RESET_SCN_TIME  

NUMBER  

The time when the reset log SCN was obtained to create the dictionary  

ENABLED_THREAD
_MAP  

RAW(16)  

Bit map of currently enabled threads when the dictionary was created  

INFO  

VARCHAR2(32)  

Informational/Status message BAD_DATE indicates that the SCN of the dictionary file does not match the SCN range of the log files  

STATUS  

NUMBER  

A NULL indicates a valid dictionary file for the list of log files. A non-NULL value indicates further information is contained in the INFO column as a text string  

V$LOGMNR_LOGS

This view contains log information.

Column   Datatype   Description  

LOG_ID  

NUMBER  

Identifies the log file. The value of this field is also reported in the LOG_ID column of the V$LOG  

FILENAME  

VARCHAR2(513)  

The filename  

LOW_TIME  

DATE  

The oldest date of any records in the file  

HIGH_TIME  

DATE  

The most recent date of any records in the file  

DB_ID  

NUMBER  

The database ID  

DB_NAME  

VARCHAR2(8)  

The name of the database  

RESET_SCN  

NUMBER  

The reset log SCN when the log was created  

RESET_SCN_TIME  

NUMBER  

The time when the reset log SCN was obtained to create the log  

THREAD_ID  

NUMBER  

The thread number  

THREAD_SQN  

NUMBER  

The thread sequence number  

LOW_SCN  

NUMBER  

SCN allocated when log switched into  

NEXT_SCN  

NUMBER  

SCN after this log. Low SCN of the next log  

INFO  

VARCHAR2(32)  

Informational message. A value of MISSING_LOGFILE will be assigned to a row entry where a needed logfile is missing from the list of log files  

STATUS  

NUMBER  

Indicates the status of a logfile. A NULL value indicates a valid logfile; a non-NULL value indicates further information is contained in the INFO column as a text string. All logfiles successfully added to the file list will have a status value of NULL  

V$LOGMNR_PARAMETERS

This view contains log information.

Column   Datatype   Description  

START_DATE  

DATE  

The date to start search at  

END_DATE  

DATE  

The date to end search at  

START_SCN  

NUMBER  

The system change number to start search  

END_SCN  

NUMBER  

The system change number to end search  

INFO  

VARCHAR2(32)  

An informational message  

STATUS  

NUMBER  

The status. A NULL value indicates parameters are valid. A non-NULL value indicates further information is contained in the INFO column as a text string  

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

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_UNION_CURRENCY; NLS_NCHAR_CHARACTERSET
NLS_COMP  

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" and "V$SQL".

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$OBSOLETE_PARAMETER

This view lists obsolete parameters. If any value is true, you should examine why.

Column   Datatype   Description  

NAME  

VARCHAR2(64)  

The name of the parameter  

ISSPECIFIED  

VARCHAR2(5)  

Whether the parameter was specified in the config file  

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

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$PARALLEL_DEGREE_LIMIT_MTH

This view displays all available parallel degree limit resource allocation methods.

Column   Datatype   Description  

NAME  

VARCHAR2(40)  

The name of the parallel degree limit resource allocation method  

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

Column   Datatype   Description  

FILE#  

NUMBER  

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

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 Oracle8i Parallel Server Concepts and Administration.

V$PQ_SESSTAT

This view lists session statistics for parallel queries.

Note: This view will be obsoleted in a future release.

Column   Datatype   Description  

STATISTIC  

VARCHAR2(30)  

Name of the statistic. See Table 3-7  

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-7 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-7 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 execution servers on an instance.

Note: This view will be replaced/obsoleted in a future release by a new view called V$PX_PROCESS.

Column   Datatype   Description  

SLAVE_NAME  

VARCHAR2(4)  

Name of the parallel execution server  

STATUS  

VARCHAR2(4)  

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

SESSIONS  

NUMBER  

The number of sessions that have used this parallel execution 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.

Note: This view will be replaced/obsoleted in a future release by a new view called V$PX_PROCESS_SYSSTAT.

Column   Datatype   Description  

STATISTIC  

VARCHAR2(30)  

Name of the statistic. See Table 3-8  

VALUE  

NUMBER  

The value of the statistic  

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_SYSSTAT to view the number of slave processes used, and other information for the system.

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

Servers Busy  

Number of currently busy servers on this instance  

Servers Idle  

Number of currently idle servers on this instance  

Servers Highwater  

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

Server Sessions  

Total number of operations executed in all servers on this instance  

Servers Started  

Total number of servers started on this instance  

Servers Shutdown  

Total number of servers shutdown on this instance  

Servers Cleaned Up  

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

Queries Initiated  

Total number of parallel queries initiated on this instance  

DML Initiated  

Total number of parallel DML operations that were initiated  

DFO Trees  

Total number of DFO trees executed on this instance  

Local Msgs Sent  

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

Distr Msgs Sent  

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

Local Msgs Recv'd  

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

Distr Msgs Recv'd  

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

V$PQ_TQSTAT

This view contains statistics on parallel execution 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 execution server at each stage of the execution tree. This view can help determine skew problems in a query's execution.

Note: This view will be renamed V$PX_TQSTAT in a future release.

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$PROXY_ARCHIVEDLOG

This view contains descriptions of archived log backups which are taken with a new feature called Proxy Copy. Each row represents a backup of one archived log.

Column   Datatype   Description  

RECID  

NUMBER  

Proxy copy record ID  

STAMP  

NUMBER  

Proxy copy record stamp  

DEVICE_TYPE  

VARCHAR2(17)  

Type of the device on which the copy resides  

HANDLE  

VARCHAR2(513)  

Proxy copy handle identifies the copy for restore  

COMMENTS  

VARCHAR2(81)  

Comment returned by the operating system or storage subsystem. This value is informational only; not needed for restore  

MEDIA  

VARCHAR2(65)  

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

DELETED  

VARCHAR2(3)  

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

THREAD#  

NUMBER  

Redo thread number  

SEQUENCE#  

NUMBER  

Redo log sequence number  

RESETLOGS
_CHANGE#  

NUMBER  

Resetlogs change number 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 number in the archived log  

FIRST_TIME  

DATE  

Timestamp of the first change  

NEXT_CHANGE#  

NUMBER  

First change number 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  

The completion time  

ELAPSED_SECONDS  

NUMBER  

The number of elapsed seconds  

V$PROXY_DATAFILE

This view contains descriptions of datafile and controlfile backups which are taken with a new feature called Proxy Copy. Each row represents a backup of one database file.

Column   Datatype   Description  

RECID  

NUMBER  

Proxy copy record ID  

STAMP  

NUMBER  

Proxy copy record stamp  

DEVICE_TYPE  

VARCHAR2(17)  

Type of the device on which the copy resides  

HANDLE  

VARCHAR2(513)  

Proxy copy handle identifies the copy for restore  

COMMENTS  

VARCHAR2(81)  

Comment returned by the operating system or storage subsystem. This value is informational only; not needed for restore  

MEDIA  

VARCHAR2(65)  

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

MEDIA_POOL  

NUMBER  

The media pool in which the copy resides. This is the same value that was entered in the POOL operand of the Recovery Manager BACKUP command  

TAG  

VARCHAR2(32)  

Proxy copy tag  

DELETED  

VARCHAR2(3)  

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

FILE#  

NUMBER  

Absolute datafile number, or 0 if this is a controlfile backup  

CREATION_CHANGE#  

NUMBER  

Datafile creation change number  

CREATION_TIME  

DATE  

Datafile creation Timestamp  

RESETLOGS_CHANGE#  

NUMBER  

Resetlogs change number 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 number 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  

The highest change in any block of the file, if known  

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  

INCREMENTAL_LEVEL  

NUMBER  

0 if this backup is part of an incremental backup strategy, otherwise NULL  

ONLINE_FUZZY  

VARCHAR2(3)  

YES/NO. If set to YES, this copy was made after a crash or offline immediate (or is a copy of a copy which was taken improperly while the database was 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. Note that the BEGIN BACKUP/END BACKUP technique is used internally when proxy copies of open files are created. Recovery will need to apply all redo up to the end backup marker to make this copy consistent  

BLOCKS  

NUMBER  

Size of the 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  

If file# is 0 (ie, this is a controlfile backup), the RECID of the oldest offline range record in this controlfile copy. 0 for datafile copies  

START_TIME  

DATE  

The starting time  

COMPLETION_TIME  

DATE  

The completion time  

ELAPSED_SECONDS  

NUMBER  

The number of elapsed seconds  

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$PX_PROCESS

This view contains information about the sessions running parallel execution.

Column   Datatype   Description  

SERVER_NAME  

VARCHAR2(4)  

The name of the parallel server (P000, P001, etc)  

STATUS  

VARCHAR2(9)  

The state of the parallel server. Either In Use or Available  

PID  

NUMBER  

The process identifier  

SPID  

VARCHAR2(9)  

The OS process ID  

SID  

NUMBER  

The session ID of slave, if in use  

SERIAL#  

NUMBER  

The session serial number of slave, if in use  

V$PX_PROCESS_SYSSTAT

This view contains information about the sessions running parallel execution.

Column   Datatype   Description  

STATISTIC  

VARCHAR2(30)  

The name of the statistic  

VALUE  

NUMBER  

The value of the statistic  

The STATISTIC column's values are:

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

Servers In Use  

The number of PX servers currently performing parallel operations  

Servers Available  

The number of PX servers available to perform parallel operations  

Servers Started  

The number of times the system has had to create a PX server process  

Server Shutdown  

The number of times a PX server process has been shutdown. A PX server process will be shutdown if it has not been used recently. The length of time it may remain "Available" is controlled by the initialization parameter PARALLEL_SERVER_IDLE_TIME

If this value is large, consider increasing the parameter. This will increase performance due to avoiding the latency of PX server process creation  

Servers HWM  

The maximum number of concurrent PX server processes

If this number is equal to the initialization parameter PARALLEL_MAX_SERVERS, consider increasing the parameter. This could allow you to increase your throughput, especially if your system is under-utilized and the V$SYSSTAT statistic "Parallel operations downgraded to serial" is large  

Servers Cleaned Up  

The number of times PMON had to clean up a PX server. This should only happen during abnormal termination of a parallel operation

If this number is large, it is recommended that the cause be determined  

Sessions  

The total number of sessions created by all PX servers  

Memory Chunks
Allocs  

The number of large memory chunks allocated by PX servers  

Memory Chunks Freed  

The number of large memory chunks freed  

Memory Chunks
Current  

The number of large memory chunks currently being used  

Memory Chunks HWM  

The maximum number of concurrently allocated chunks  

Buffers allocated  

The number of times a message buffer has been allocated  

Buffers freed  

The number of times a message buffer has been freed  

Buffers Current  

The number of message buffers currently being used  

Buffers HWM  

The maximum number of concurrently allocated message buffers  

V$PX_SESSION

This view contains information about the sessions running parallel execution.

Column   Datatype   Description  

SADDR  

RAW(4)  

Session address  

SID  

NUMBER  

Session identifier  

SERIAL#  

NUMBER  

Session serial number  

QCSID  

NUMBER  

Session identifier of the parallel coordinator  

QCSERIAL#  

NUMBER  

Session serial number of the parallel coordinator  

QCINST_ID  

NUMBER  

Instance number on which the parallel coordinator is running  

SERVER_GROUP  

NUMBER  

The logical group of servers to which this parallel server process belongs  

SERVER_SET  

NUMBER  

The logical set of servers that this parallel server process belongs to. A single server group will have at most two server sets  

SERVER#  

NUMBER  

The logical number of a parallel server process within a server set  

DEGREE  

NUMBER  

The degree of parallelism being used by the server set  

REQ_DEGREE  

NUMBER  

The degree of parallelism that was requested by the user when the statement was issued and prior to any resource, multi-user, or load balancing reductions  

V$PX_SESSTAT

This view contains information about the sessions running parallel execution.

Column   Datatype   Description  

SADDR  

RAW(4)  

Session address  

SID  

NUMBER  

Session identifier  

SERIAL#  

NUMBER  

Session serial number  

QCSID  

NUMBER  

Session identifier of the parallel coordinator  

QCSERIAL#  

NUMBER  

Session serial number of the parallel coordinator  

QCINST_ID  

NUMBER  

Instance number on which the parallel coordinator is running  

SERVER_GROUP  

NUMBER  

The logical group of servers to which this parallel server process belongs  

SERVER_SET  

NUMBER  

The logical set of servers that this parallel server process belongs to. A single server group will have at most two server sets  

SERVER#  

NUMBER  

The logical number of a parallel server process within a server set  

DEGREE  

NUMBER  

The degree of parallelism being used by the server set  

REQ_DEGREE  

NUMBER  

The degree of parallelism that was requested by the user when the statement was issued and prior to any resource, multi-user, or load balancing reductions  

STATISTIC#  

NUMBER  

Statistic number (identifier)  

VALUE  

NUMBER  

Statistic value  

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), DISPATCHER  

QUEUED  

NUMBER  

Number of items in the queue  

WAIT  

NUMBER  

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

TOTALQ  

NUMBER  

Total number of items that have ever been in the queue  

V$RECOVER_FILE

This view displays the status of files needing media recovery.

Column   Datatype   Description  

FILE#  

NUMBER  

File identifier number  

ONLINE  

VARCHAR2  

Online status: ONLINE, OFFLINE  

ERROR  

VARCHAR2  

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

CHANGE#  

NUMBER  

SCN where recovery must start  

TIME  

DATE  

Time of SCN when recovery must start  

V$RECOVERY_FILE_STATUS

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

Column   Datatype   Description  

FILENUM  

NUMBER  

The number of the file being recovered  

FILENAME  

VARCHAR2(257)  

The filename of the datafile being recovered  

STATUS  

VARCHAR2(13)  

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

For further information, see Oracle8i 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".

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"  

For further information, see Oracle8i 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(32)  

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 Oracle8i 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 Oracle8i 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$RESERVED_WORDS

This view gives a list of all the keywords that are used by the PL/SQL compiler. This view helps developers to determine whether a word is already being used as a keyword in the language.

Column   Datatype   Description  

KEYWORD  

VARCHAR2(64)  

The name of the keyword  

LENGTH  

NUMBER  

The length of the keyword  

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.  

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".  
DML_LOCKS   DML_LOCKS For more information on this parameter see "DML_LOCKS".  
ENQUEUE_LOCKS   This value is computed by Oracle. Use the V$ENQUEUE_LOCK view (described on ) to obtain more information about the enqueue locks.  
ENQUEUE_RESOURCES   ENQUEUE_RESOURCES For more information on this parameter see "ENQUEUE_RESOURCES".  
LM_PROCESSES   LM_PROCS For more information on this parameter see "LM_PROCS".  
LM_RESOURCES   LM_RESS For more information on this parameter see "LM_RESS".  
LM_LOCKS   LM_LOCKS For more information on this parameter see "LM_LOCKS".  
MTS_MAX_SERVERS   MTS_MAX_SERVERS For more information on this parameter see "MTS_MAX_SERVERS".  
PARALLEL_SLAVES   PARALLEL_MAX_SERVERS For more information on this parameter, see "PARALLEL_MAX_SERVERS".  
PROCESSES   PROCESSES For more information on this parameter, see "PROCESSES".  
ROLLBACK_SEGMENTS   MAX_ROLLBACK_SEGMENTS For more information on this parameter, see "MAX_ROLLBACK_SEGMENTS".  
SESSIONS   SESSIONS For more information on this parameter, see "SESSIONS"  
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"  

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  

DLM_REQUESTS  

NUMBER  

The number of DLM requests  

DLM_CONFLICTS  

NUMBER  

The number of DLM conflicts  

DLM_RELEASES  

NUMBER  

The number of DLM releases  

V$ROWCACHE_PARENT

This view displays information for parent objects in the data dictionary. There is one row per lock owner, and one waiter for each object. This row shows the mode held or requested. For objects with no owners or waiters, a single row is displayed.

Column   Datatype   Description  

INDX  

NUMBER  

Index of the row  

HASH  

NUMBER  

The hash value  

ADDRESS  

RAW(4)  

The address of the parent object  

CACHE#  

NUMBER  

The parent cache ID  

CACHE_NAME  

VARCHAR2(64)  

The parent cache name  

EXISTENT  

VARCHAR2(1)  

Whether the object is an existing object  

LOCK_MODE  

NUMBER  

The mode the lock is held in  

LOCK_REQUEST  

NUMBER  

The mode the lock is requested in  

TXN  

RAW(4)  

The transaction currently locking the object  

SADDR  

RAW(4)  

The address of the session  

INST_LOCK_REQUEST  

NUMBER  

Relevant only for Parallel Server. The mode instance lock is being requested in  

INST_LOCK_RELEASE  

NUMBER  

Relevant only for Parallel Server. Whether the instance lock needs to be released  

INST_LOCK_TYPE  

VARCHAR2(2)  

Relevant only for Parallel Server. The type of instance lock  

INST_LOCK_ID1  

RAW(4)  

Relevant only for Parallel Server. The ID associated with the instance lock  

INST_LOCK_ID2  

RAW(4)  

Relevant only for Parallel Server. The ID associated with the instance lock  

KEY  

RAW(100)  

Relevant only for Parallel Server. The contents of the key  

V$ROWCACHE_SUBORDINATE

This view displays information for subordinate objects in the data dictionary.

Column   Datatype   Description  

INDX  

NUMBER  

The index  

HASH  

NUMBER  

The hash value  

ADDRESS  

RAW(4)  

The address of the subordinate object  

CACHE#  

NUMBER  

The parent cache ID  

SUBCACHE#  

NUMBER  

The subcache ID  

SUBCACHE_NAME  

VARCHAR2(64)  

The subcache name  

EXISTENT  

VARCHAR2(1)  

Whether the object is an existing object  

PARENT  

RAW(4)  

The address of the parent object  

KEY  

RAW(100)  

The contents of the key  

V$RSRC_CONSUMER_GROUP

This view displays data related to the currently active resource consumer groups.

Column   Datatype   Description  

NAME  

VARCHAR2(32)  

The name of the consumer group  

ACTIVE_SESSIONS  

NUMBER  

The number of currently active sessions in this consumer group  

EXECUTION_WAITERS  

NUMBER  

The number of currently active sessions waiting for an execution quantum  

REQUESTS  

NUMBER  

The total number of requests that were executed in this consumer group  

CPU_WAIT_TIME  

NUMBER  

The total amount of time that sessions waited for CPU  

CPU_WAITS  

NUMBER  

The number of times all sessions in this consumer group had to wait for CPU  

CONSUMED_CPU_TIME  

NUMBER  

The total amount of CPU time consumed by all sessions in this consumer group  

YIELDS  

NUMBER  

The number of times sessions in this consumer group had to yield the CPU  

SESSIONS_QUEUED  

NUMBER  

The count of currently queued sessions waiting to become active  

V$RSRC_CONSUMER_GROUP_CPU_MTH

This view shows all available resource allocation methods for resource consumer groups.

Column   Datatype   Description  

NAME  

VARCHAR2(40)  

The name of the CPU resource allocation method  

V$RSRC_PLAN

This view displays the names of all currently active resource plans.

Column   Datatype   Description  

NAME  

VARCHAR2(32)  

The name of the resource plan  

V$RSRC_PLAN_CPU_MTH

This view shows all available CPU resource allocation methods for resource plans.

Column   Datatype   Description  

NAME  

VARCHAR2(32)  

The name of the resource allocation method  

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

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 not -1  

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 not -1  

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 not -1  

LOGON_TIME  

DATE  

Time of logon  

LAST_CALL_ET  

NUMBER  

The last call  

PDML_STATUS  

VARCHAR2(8)  

If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML.  

PDDL_STATUS  

VARCHAR2(8)  

If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL.  

PDML_ENABLED  

VARCHAR2(3)  

This column has been replaced by PDML_ENABLED and PDML_STATUS. See above.  

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  

RESOURCE_CONSUMER_GROUP  

VARCHAR2(32)  

Name of the session's current resource consumer group  

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

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:

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:

For more information on session waits, 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  

SECONDS_IN
_WAIT  

NUMBER  

The seconds in wait  

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

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

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

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" and "V$SYSSTAT".

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  

CPU used when call started  

CR blocks created  

Cached Commit SCN referenced  

Commit SCN referenced  

Current blocks converted for CR  

DBWR buffers scanned  

DBWR checkpoint buffers written  

DBWR checkpoints  

DBWR forced writes  

DBWR free buffers found  

DBWR lru scans  

DBWR make free requests  

DBWR revisited being-written buffer  

DBWR skip hot writes  

DBWR summed scan depth  

DBWR transaction table writes  

DBWR undo block writes  

DDL statements parallelized  

DFO trees parallelized  

DML statements parallelized  

OS All other sleep time  

OS Chars read and written  

OS Data page fault sleep time  

OS Input blocks  

OS Involuntary context switches  

OS Kernel page fault sleep time  

OS Major page faults  

OS Messages received  

OS Messages sent  

OS Minor page faults  

OS Other system trap CPU time  

OS Output blocks  

OS Process heap size  

OS Process stack size  

OS Signals received  

OS Swaps  

OS System call CPU time  

OS System calls  

OS Text page fault sleep time  

OS User level CPU time  

OS User lock wait sleep time  

OS Voluntary context switches  

OS Wait-cpu (latency) time  

PX local messages received  

PX local messages sent  

PX remote messages received  

PX remote messages sent  

Parallel operations downgraded to serial  

SQL*Net roundtrips to/from client  

SQL*Net roundtrips to/from dblink  

Unnecessary process cleanup for SCN batching  

background checkpoints completed  

background checkpoints started  

background timeouts  

buffer is not pinned count  

buffer is pinned count  

bytes received via SQL*Net from client  

bytes received via SQL*Net from dblink  

bytes sent via SQL*Net to client  

bytes sent via SQL*Net to dblink  

calls to get snapshot scn: kcmgss  

calls to kcmgas  

calls to kcmgcs  

calls to kcmgrs  

change write time  

cleanouts and rollbacks - consistent read gets  

cleanouts only - consistent read gets  

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  

cursor authentications  

db block changes  

db block gets  

deferred (CURRENT) block cleanout applications  

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 convert time  

global cache convert timeouts  

global cache converts  

global cache cr block receive time  

global cache cr blocks received  

global cache cr read from disk  

global cache cr timeouts  

global cache defers  

global cache fairness down converts  

global cache freelist waits  

global cache get time  

global cache convert time  

global cache queued converts  

global lock async converts  

global lock async gets  

global lock convert time  

global lock converts (async)  

global lock converts (non async)  

global lock get time  

global cache hash latch waits  

global lock gets (async)  

global lock gets (non async)  

global lock releases  

global lock sync converts  

global lock sync gets  

hot buffers moved to head of LRU  

immediate (CR) block cleanout applications  

immediate (CURRENT) block cleanout applications  

instance recovery database freeze count  

kcmccs called get current scn  

kcmccs read scn without going to DLM  

kcmccs waited for batching  

logons cumulative  

logons current  

messages received  

messages sent  

native hash arithmetic execute  

native hash arithmetic fail  

next scns gotten without going to DLM  

no buffer to keep pinned count  

no work-consistent read gets  

opened cursors cumulative  

opened cursors current  

opens of replaced files  

opens requiring cache replacement  

parse count (hard)  

parse count (total)  

parse time cpu  

parse time elapsed  

physical reads  

physical writes  

physical reads direct  

physical writes direct  

physical writes non-checkpoint  

physical writes non-checkpoint  

pinned buffers inspected  

process last non-idle time  

queries parallelized  

recovery array read time  

recovery array reads  

recovery blocks read  

recursive calls  

recursive cpu usage  

redo blocks written  

redo buffer allocation retries  

redo entries  

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  

rollback changes-undo records applied  

rollbacks only-consistent read gets  

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  

transaction lock background get time  

transaction lock background gets  

transaction lock foreground requests  

transaction lock foreground wait time  

transaction rollbacks  

transaction tables consistent read rollbacks  

transaction tables consistent reads-undo records applied  

user calls  

user commits  

user rollbacks  

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

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

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

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$TEMPFILE

This view displays tempfile information.

Column   Datatype   Description  

FILE#  

NUMBER  

The absolute file number  

CREATION_CHANGE#  

NUMBER  

The creation System Change Number  

CREATION_TIME  

DATE  

The creation time  

TS#  

NUMBER  

The tablespace number  

RFILE#  

NUMBER  

The relative file number in tablespace  

STATUS  

VARCHAR2(7)  

The status of the file (offline/online)  

ENABLED  

VARCHAR2(10)  

Enabled for read and/or write  

BYTES  

NUMBER  

The size of the file in bytes (from File Header)  

BLOCKS  

NUMBER  

The size of the file in blocks (from File Header)  

CREATE_BYTES  

NUMBER  

The creation size of the file (in bytes)  

BLOCK_SIZE  

NUMBER  

The block size for the file  

NAME  

VARCHAR2(513)  

The name of the file  

V$TEMPORARY_LOBS

This view displays temporary lobs.

Column   Datatype   Description  

SID  

NUMBER  

Session ID  

CACHE_LOBS  

NUMBER  

Number cache temp lobs  

NOCACHE_LOBS  

NUMBER  

Number of nocache temp lobs  

V$TEMP_EXTENT_MAP

This view displays the status of each unit for all temporary tablespaces.

Column   Datatype   Description  

TABLESPACE_NAME  

NUMBER  

Name of tablespace this unit belongs to  

FILE_ID  

NUMBER  

Absolute file number  

BLOCK_ID  

NUMBER  

Begin block number for this unit  

BYTES  

NUMBER  

Bytes in extent  

BLOCKS  

NUMBER  

Blocks in extent  

OWNER  

NUMBER  

Which instance own this unit (string)  

RELATIVE_FNO  

NUMBER  

The relative file number  

V$TEMP_EXTENT_POOL

This view displays the state of temporary space cached and used for a given instance. Note that loading of the temporary space cache is lazy, and that instances can be dormant. Use GV$TEMP_EXTENT_POOL for information about all instances.

Column   Datatype   Description  

TABLESPACE_NAME  

VARCHAR2(30)  

Name of the tablespace  

FILE_ID  

NUMBER  

Absolute file number  

EXTENTS_CACHED  

NUMBER  

How many extents have been cached  

EXTENTS_USED  

NUMBER  

How many extents are actually being used  

BLOCKS_CACHED  

NUMBER  

How many blocks are cached  

BLOCKS_USED  

NUMBER  

How many blocks are used  

BYTES_CACHED  

NUMBER  

How many bytes are cached  

BYTES_USED  

NUMBER  

How many bytes used  

RELATIVE_FNO  

NUMBER  

The relative file number  

V$TEMP_PING

The view V$TEMP_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 reuse block range cross instance call for this file  

RBR_FORCED_WRITE  

NUMBER  

Number of blocks written due to reuse 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 reuse 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  

OP_2_SS  

NUMBER  

Number of pcm locks ss locks opened. 0 in Oracle 8.1  

V$TEMP_SPACE_HEADER

This view displays aggregate information per file per temporary tablespace regarding how much space is currently being used and how much is free as per the space header.

Column   Datatype   Description  

TABLESPACE_NAME  

VARCHAR2(30)  

The name of the temporary tablespace  

FILE_ID  

NUMBER  

The absolute file number  

BYTES_USED  

NUMBER  

How many bytes are in use  

BLOCKS_USED  

NUMBER  

How many blocks are in use  

BYTES_FREE  

NUMBER  

How many bytes are free  

BLOCKS_FREE  

NUMBER  

How many blocks are free  

RELATIVE_FNO  

NUMBER  

The relative file number for the file  

V$TEMPSTAT

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$THREAD

This view contains thread information from the control file.

Column   Datatype   Description  

THREAD#  

NUMBER  

Thread number  

STATUS  

VARCHAR2  

Thread status: OPEN, CLOSED  

ENABLED  

VARCHAR2  

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

ENABLE_CHANGE#  

NUMBER  

SCN at which thread was enabled  

ENABLE_TIME  

DATE  

Time of enable SCN  

DISABLE_CHANGE#  

NUMBER  

SCN at which thread was disabled  

DISABLE_TIME  

DATE  

Time of disable SCN  

GROUPS  

NUMBER  

Number of log groups assigned to this thread  

INSTANCE  

VARCHAR2  

Instance name, if available  

OPEN_TIME  

DATE  

Last time the thread was opened  

CURRENT_GROUP#  

NUMBER  

Current log group  

SEQUENCE#  

NUMBER  

Sequence number of current log  

CHECKPOINT_CHANGE#  

NUMBER  

SCN at last checkpoint  

CHECKPOINT_TIME  

DATE  

Time of last checkpoint  

V$TIMER

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

Column   Datatype   Description  

HSECS  

NUMBER  

Elapsed time in hundredths of a second  

V$TRANSACTION

This view lists the active transactions in the system.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of transaction state object  

XIDUSN  

NUMBER  

Undo segment number  

XIDSLOT  

NUMBER  

Slot number  

XIDSQN  

NUMBER  

Sequence number  

UBAFIL  

NUMBER  

Undo block address (UBA) filenum  

UBABLK  

NUMBER  

UBA block number  

UBASQN  

NUMBER  

UBA sequence number  

UBAREC  

NUMBER  

UBA record number  

STATUS  

VARCHAR2(16)  

Status  

START_TIME  

VARCHAR2(20)  

Start time (wall clock)  

START_SCNB  

NUMBER  

Start system change number (SCN) base  

START_SCNW  

NUMBER  

Start SCN wrap  

START_UEXT  

NUMBER  

Start extent number  

START_UBAFIL  

NUMBER  

Start UBA file number  

START_UBABLK  

NUMBER  

Start UBA block number  

START_UBASQN  

NUMBER  

Start UBA sequence number  

START_UBAREC  

NUMBER  

Start UBA record number  

SES_ADDR  

RAW(4)  

User session object address  

FLAG  

NUMBER  

Flag  

SPACE  

VARCHAR2(3)  

"Yes", if a space transaction  

RECURSIVE  

VARCHAR2(3)  

"Yes", if a recursive transaction  

NOUNDO  

VARCHAR2(3)  

"Yes" if a no undo transaction  

PTX  

VARCHAR 2(3)  

YES if parallel transaction, otherwise set to NO  

PRV_XIDUSN  

NUMBER  

Previous transaction undo segment number  

PRV_XIDSLT  

NUMBER  

Previous transaction slot number  

PRV_XIDSQN  

NUMBER  

Previous transaction sequence number  

PTX_XIDUSN  

NUMBER  

Rollback segment number of the parent XID  

PTX_XIDSLT  

NUMBER  

Slot number of the parent XID  

PTX_XIDSQN  

NUMBER  

Sequence number of the parent XID  

DSCN_B  

NUMBER  

Dependent SCN base  

DSCN_W  

NUMBER  

Dependent SCN wrap  

USED_UBLK  

NUMBER  

Number of undo blocks used  

USED_UREC  

NUMBER  

Number of undo records used  

LOG_IO  

NUMBER  

Logical I/O  

PHY_IO  

NUMBER  

Physical I/O  

CR_GET  

NUMBER  

Consistent gets  

CR_CHANGE  

NUMBER  

Consistent changes  

V$TRANSACTION_ENQUEUE

V$TRANSACTION_ENQUEUE displays locks owned by transaction state objects.

Column   Datatype   Description  

ADDR  

RAW(4)  

Address of lock state object  

KADDR  

RAW(4)  

Address of lock  

SID  

NUMBER  

Identifier for session holding or acquiring the lock  

TYPE  

VARCHAR2(2)  

Type of lock. TX = transaction enqueue.  

ID1  

NUMBER  

Lock identifier #1 (depends on type)  

ID2  

NUMBER  

Lock identifier #2 (depends on type)  

LMODE  

NUMBER  

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

REQUEST  

NUMBER  

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

CTIME  

NUMBER  

Time since current mode was granted  

BLOCK  

NUMBER  

The lock is blocking another lock  

V$TYPE_SIZE

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

Column   Datatype   Description  

COMPONENT  

VARCHAR2  

Component name, such as segment or buffer header  

TYPE  

VARCHAR2  

Component type  

DESCRIPTION  

VARCHAR2  

Description of component  

TYPE_SIZE  

NUMBER  

Size of component  

V$VERSION

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

Column   Datatype   Description  

BANNER  

VARCHAR2  

Component name and version number  

V$WAITSTAT

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

Column   Datatype   Description  

CLASS  

VARCHAR2  

Class of block  

COUNT  

NUMBER  

Number of waits by this OPERATION for this CLASS of block  

TIME  

NUMBER  

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




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index