3
Dynamic Performance (V$) Views

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

The following topics are included in this chapter:

Dynamic Performance Views

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

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

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

V$ Views

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

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

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

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

GV$ Views

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

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

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

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

Restrictions on GV$ Views

GV$ views have the following restrictions:

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

Access to the Dynamic Performance Tables

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

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

View Descriptions

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

FILEXT$

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

Column Datatype Description

FILE#

NUMBER

File identifier

MAXEXTEND

NUMBER

Value from the MAXSIZE parameter

INC

NUMBER

Value from the NEXT parameter

Column	Datatype	Description
FILE#	NUMBER	File identifier
MAXEXTEND	NUMBER	Value from the MAXSIZE parameter
INC	NUMBER	Value from the NEXT parameter

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

V$ACCESS

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

Column Datatype Description

SID

NUMBER

Session number that is accessing an object

OWNER

VARCHAR2(64)

Owner of the object

OBJECT

VARCHAR2(1000)

Name of the object

TYPE

VARCHAR2(12)

Type identifier for the object

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

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

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

V$ARCHIVE

This view contains information on redo log files in need of archiving. Each row provides information for one thread. This information is also available in V$LOG. Oracle recommends that you use V$LOG. For more information, see "V$LOG" on page 3-57.

Column Datatype Description

GROUP#

NUMBER

Log file group number

THREAD#

NUMBER

Log file thread number

SEQUENCE#

NUMBER

Log file sequence number

CURRENT

VARCHAR2(3)

Archive log currently in use

FIRST_CHANGE#

NUMBER

First SCN stored in the current log

Column	Datatype	Description
GROUP#	NUMBER	Log file group number
THREAD#	NUMBER	Log file thread number
SEQUENCE#	NUMBER	Log file sequence number
CURRENT	VARCHAR2(3)	Archive log currently in use
FIRST_CHANGE#	NUMBER	First SCN stored in the current log

V$ARCHIVE_DEST

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

Column Datatype Description

ARCMODE

VARCHAR2(12)

Archiving mode:

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

STATUS

VARCHAR2(8)

Status:

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

DESTINATION

VARCHAR2(256)

Destination text string

Column	Datatype	Description
ARCMODE	VARCHAR2(12)	Archiving mode: MUST SUCCEED: This is a must-succeed destination BEST-EFFORT: This is a best-effort destination
STATUS	VARCHAR2(8)	Status: NORMAL: This destination is normal DISABLED: This destination has been disabled
DESTINATION	VARCHAR2(256)	Destination text string

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

V$ARCHIVED_LOG

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

Column Datatype Description

RECID

NUMBER

Archived log record ID

STAMP

NUMBER

Archived log record stamp

NAME

VARCHAR2(512)

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

THREAD#

NUMBER

Redo thread number

SEQUENCE#

NUMBER

Redo log sequence number

RESETLOGS_CHANGE#

NUMBER

Resetlogs change# of the database when this log was written

RESETLOGS_TIME

DATE

Resetlogs time of the database when this log was written

FIRST_CHANGE#

NUMBER

First change# in the archived log

FIRST_TIME

DATE

Timestamp of the first change

NEXT_CHANGE#

NUMBER

First change in the next log

NEXT_TIME

DATE

Timestamp of the next change

BLOCKS

NUMBER

Size of the archived log in blocks

BLOCK_SIZE

NUMBER

Redo log block size

COMPLETION_TIME

DATE

Time when the archiving completed

DELETED

VARCHAR2(3)

YES/NO

Column	Datatype	Description
RECID	NUMBER	Archived log record ID
STAMP	NUMBER	Archived log record stamp
NAME	VARCHAR2(512)	Archived log file name. If set to NULL, the log file was cleared before it was archived
THREAD#	NUMBER	Redo thread number
SEQUENCE#	NUMBER	Redo log sequence number
RESETLOGS_CHANGE#	NUMBER	Resetlogs change# of the database when this log was written
RESETLOGS_TIME	DATE	Resetlogs time of the database when this log was written
FIRST_CHANGE#	NUMBER	First change# in the archived log
FIRST_TIME	DATE	Timestamp of the first change
NEXT_CHANGE#	NUMBER	First change in the next log
NEXT_TIME	DATE	Timestamp of the next change
BLOCKS	NUMBER	Size of the archived log in blocks
BLOCK_SIZE	NUMBER	Redo log block size
COMPLETION_TIME	DATE	Time when the archiving completed
DELETED	VARCHAR2(3)	YES/NO

V$BACKUP

This view displays the backup status of all online datafiles.

Column Datatype Description

FILE#

NUMBER

File identifier

STATUS

VARCHAR2(18)

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

CHANGE#

NUMBER

System change number when backup started

TIME

DATE

Time the backup started

Column	Datatype	Description
FILE#	NUMBER	File identifier
STATUS	VARCHAR2(18)	File status: NOT ACTIVE, ACTIVE (backup in progress), OFFLINE NORMAL, or description of an error
CHANGE#	NUMBER	System change number when backup started
TIME	DATE	Time the backup started

V$BACKUP_CORRUPTION

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

Column Datatype Description

RECID

NUMBER

Backup corruption record ID

STAMP

NUMBER

Backup corruption record stamp

SET_STAMP

NUMBER

Backup set stamp

SET_COUNT

NUMBER

Backup set count

PIECE#

NUMBER

Backup piece number

FILE#

NUMBER

Datafile number

BLOCK#

NUMBER

First block of the corrupted range

BLOCKS

NUMBER

Number of contiguous blocks in the corrupted range

CORRUPTION
_CHANGE#

NUMBER

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

MARKED_CORRUPT

VARCHAR2(3)

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

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.

Column	Datatype	Description
RECID	NUMBER	Backup datafile record ID
STAMP	NUMBER	Backup datafile record stamp
SET_STAMP	NUMBER	Backup set stamp
SET_COUNT	NUMBER	Backup set count
FILE#	NUMBER	Datafile number. Set to 0 for controlfile
CREATION_CHANGE#	NUMBER	Creation change of the datafile
CREATION_TIME	DATE	Creation timestamp of the datafile
RESETLOGS_CHANGE#	NUMBER	Resetlogs change# of the datafile when it was backed up
RESETLOGS_TIME	DATE	Resetlogs timestamp of the datafile when it was backed up
INCREMENTAL_LEVEL	NUMBER	(0-4) incremental backup level
INCREMENTAL_CHANGE#	NUMBER	All blocks changed after incremental change# is included in this backup. Set to 0 for a full backup
CHECKPOINT_CHANGE#	NUMBER	All changes up to checkpoint change# are included in this backup
CHECKPOINT_TIME	DATE	Timestamp of the checkpoint
ABSOLUTE_FUZZY _CHANGE#	NUMBER	Highest change# in this backup
MARKED_CORRUPT	NUMBER	Number of blocks marked corrupt
MEDIA_CORRUPT	NUMBER	Number of blocks media corrupt
LOGICALLY_CORRUPT	NUMBER	Number of blocks logically corrupt
DATAFILE_BLOCKS	NUMBER	Size of the datafile in blocks at backup time. This value is also the number of blocks taken by the datafile restarted from this backup
BLOCKS	NUMBER	Size of the backup datafile in blocks. Unused blocks are not copied to the backup
BLOCK_SIZE	NUMBER	Block size
OLDEST_OFFLINE _RANGE	NUMBER	The RECID of the oldest offline range record in this backup controlfile. 0 for datafile backups
COMPLETION_TIME	DATE	The time completed.

V$BACKUP_DEVICE

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

Column	Datatype	Description
DEVICE_TYPE	VARCHAR2(17)	Type of the backup device
DEVICE_NAME	VARCHAR2(512)	Name of the backup device

V$BACKUP_PIECE

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

Column	Datatype	Description
RECID	NUMBER	Backup piece record ID
STAMP	NUMBER	Backup piece record stamp
SET_STAMP	NUMBER	Backup set stamp
SET_COUNT	NUMBER	Backup set count
PIECE#	NUMBER	Backup piece number (1-N)
DEVICE_TYPE	VARCHAR2(17)	Type of the device on which the backup piece resides. Set to DISK for backup sets on disk. See V$BACKUP_DEVICE
HANDLE	VARCHAR2(513)	Backup piece handle identifies the backup piece on restore
COMMENTS	VARCHAR2(81)	Comment returned by the operating system or storage subsystem. Set to NULL for backup pieces on disk. This value is informational only; not needed for restore.
MEDIA	VARCHAR2(65)	Name of the media on which the backup piece resides. This value is informational only; not needed for restore.
CONCUR	VARCHAR2(3)	YES/NO, Indicates whether the piece on a media that can be accessed concurrently
TAG	VARCHAR2(32)	Backup piece tag. The tag is specified at backup set level, but stored at piece level
DELETED	VARCHAR2(3)	If set to YES indicates the piece is deleted, otherwise set to NO
START_TIME	DATE	The starting time.
COMPLETION_TIME	DATE	The completion time.
ELAPSED_SECONDS	NUMBER	The number of elapsed seconds.

V$BACKUP_REDOLOG

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

Column	Datatype	Description
RECID	NUMBER	Record ID for this row. It is an integer that identifies this row.
STAMP	NUMBER	Timestamp used with RECID to uniquely identify this row
SET_STAMP	NUMBER	One of the foreign keys for the row of the V$BACKUP_SET table that identifies this backup set
SET_COUNT	NUMBER	One of the foreign keys for the row of the V$BACKUP_SET table that identifies this backup set
THREAD#	NUMBER	Thread number for the log
SEQUENCE#	NUMBER	Log sequence number
RESETLOGS_CHANGE#	NUMBER	Change number of the last resetlogs before the log was written
RESETLOGS_TIME	DATE	Change time of the last resetlogs before the log was written. These will be the same for all logs in a backup set
FIRST_CHANGE#	NUMBER	SCN when the log was switched into. The redo in the log is at this SCN and greater
FIRST_TIME	DATE	Time allocated when the log was switched into
NEXT_CHANGE#	NUMBER	SCN when the log was switched out of. The redo in the log is below this SCN
NEXT_TIME	DATE	Time allocated when the log was switched out of
BLOCKS	NUMBER	Size of the log in logical blocks including the header block
BLOCK_SIZE	NUMBER	Size of the log blocks in bytes

V$BACKUP_SET

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

Column Datatype Description

RECID

NUMBER

Backup set record ID

STAMP

NUMBER

Backup set record timestamp

SET_STAMP

NUMBER

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

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

SET_COUNT

NUMBER

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

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

BACKUP_TYPE

VARCHAR2(1)

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

CONTROLFILE
_INCLUDED

VARCHAR2(3)

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

INCREMENTAL
_LEVEL

NUMBER

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

PIECES

NUMBER

Number of distinct backup pieces in the backup set

COMPLETION_TIME

DATE

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

ELAPSED_SECONDS

NUMBER

The number of elapsed seconds.

BLOCK_SIZE

NUMBER

Block size of the backup set

Column	Datatype	Description
RECID	NUMBER	Backup set record ID
STAMP	NUMBER	Backup set record timestamp
SET_STAMP	NUMBER	Backup set stamp. The backup set stamp and count uniquely identify the backup set. Primary key for the V$BACKUP_SET table, and the foreign key for the following tables: V$BACKUP_PIECE V$BACKUP_DATAFILE V$BACKUP_REDOLOG V$BACKUP_CORRUPTION
SET_COUNT	NUMBER	Backup set count. The backup set count is incremented by one every time a new backup set is started (if the backup set is never completed the number is "lost"). If the controlfile is recreated then the count is reset to 1. Therefore the count must be used with the stamp to uniquely identify a backup set. Primary key for the V$BACKUP_SET table, and the foreign key for the following tables: V$BACKUP_PIECE V$BACKUP_DATAFILE V$BACKUP_REDOLOG V$BACKUP_CORRUPTION
BACKUP_TYPE	VARCHAR2(1)	Type of files that are in this backup. If the backup contains archived redo logs, the value is \QL'. If this is a datafile full backup, the value is \QD'. If this is an incremental backup, the value is \QI'.
CONTROLFILE _INCLUDED	VARCHAR2(3)	Set to YES if there is a controlfile included in this backup set, otherwise set to NO.
INCREMENTAL _LEVEL	NUMBER	Location where this backup set fits into the database's backup strategy. Set to zero for full datafile backups, non-zero for incremental datafile backups, and NULL for archivelog backups.
PIECES	NUMBER	Number of distinct backup pieces in the backup set
COMPLETION_TIME	DATE	When the backup completes successfully, this is set to the completion time. This is the same time that was returned by backupEnd. If the backup is still in progress or has failed, this is set to NULL.
ELAPSED_SECONDS	NUMBER	The number of elapsed seconds.
BLOCK_SIZE	NUMBER	Block size of the backup set

V$BGPROCESS

This view describes the background processes.

Column Datatype Description

PADDR

RAW(4)

Address of the process state object

NAME

VARCHAR2

Name of this background process

DESCRIPTION

VARCHAR2

Description of the background process

ERROR

NUMBER

Error encountered

Column	Datatype	Description
PADDR	RAW(4)	Address of the process state object
NAME	VARCHAR2	Name of this background process
DESCRIPTION	VARCHAR2	Description of the background process
ERROR	NUMBER	Error encountered

V$BH

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

Column Datatype Description

FILE#

NUMBER

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

BLOCK#

NUMBER

Block number

STATUS

VARCHAR2(1)

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

XNC

NUMBER

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

LOCK_ELEMENT
_ADDR

RAW(4)

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

LOCK_ELEMENT
_NAME

NUMBER

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

LOCK_ELEMENT
_CLASS

NUMBER

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

FORCED_READS

NUMBER

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

FORCED_WRITES

NUMBER

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

DIRTY

VARCHAR2(1)

Y = block modified.

TEMP

VARCHAR2(1)

Y = temporary block

PING

VARCHAR2(1)

Y = block pinged

STALE

VARCHAR2(1)

Y = block is stale

DIRECT

VARCHAR2(1)

Y = direct block

NEW

VARCHAR2(1)

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

OBJD

NUMBER

Database object number of the block that the buffer represents

Column	Datatype	Description
FILE#	NUMBER	Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES)
BLOCK#	NUMBER	Block number
STATUS	VARCHAR2(1)	FREE= not currently in use XCUR= exclusive SCUR= shared current CR= consistent read READ= being read from disk MREC= in media recovery mode IREC= in instance recovery mode
XNC	NUMBER	Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete but is retained for historical compatibility.
LOCK_ELEMENT _ADDR	RAW(4)	The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT _NAME	NUMBER	The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT _CLASS	NUMBER	The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
FORCED_READS	NUMBER	Number of times the block had to be made re-read from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on this block in lock mode.
FORCED_WRITES	NUMBER	Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the PCM lock on the block in conflicting mode.
DIRTY	VARCHAR2(1)	Y = block modified.
TEMP	VARCHAR2(1)	Y = temporary block
PING	VARCHAR2(1)	Y = block pinged
STALE	VARCHAR2(1)	Y = block is stale
DIRECT	VARCHAR2(1)	Y = direct block
NEW	VARCHAR2(1)	Always set to N. This column is obsolete but is retained for historical compatibility
OBJD	NUMBER	Database object number of the block that the buffer represents

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

V$BUFFER_POOL

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

Column Datatype Description

INST_ID

NUMBER

Instance ID

ID

NUMBER

Buffer pool ID number

NAME

VARCHAR2

Buffer pool name

LO_SETID

NUMBER

Low set ID number

HI_SETID

NUMBER

High set ID number

SET_COUNT

NUMBER

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

SIZE

NUMBER

Number of buffers allocated to the buffer pool

LO_BNUM

NUMBER

Low buffer number for this pool

HI_BNUM

NUMBER

High buffer number for this pool

Column	Datatype	Description
INST_ID	NUMBER	Instance ID
ID	NUMBER	Buffer pool ID number
NAME	VARCHAR2	Buffer pool name
LO_SETID	NUMBER	Low set ID number
HI_SETID	NUMBER	High set ID number
SET_COUNT	NUMBER	Number of sets in this buffer pool. This is HI_SETID - LO_SETID + 1
SIZE	NUMBER	Number of buffers allocated to the buffer pool
LO_BNUM	NUMBER	Low buffer number for this pool
HI_BNUM	NUMBER	High buffer number for this pool

V$CACHE

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

Column Datatype Description

FILE#

NUMBER

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

BLOCK#

NUMBER

Block number

STATUS

VARCHAR2(1)

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

XNC

NUMBER

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

NAME

VARCHAR2(30)

Name of the database object containing the block

KIND

VARCHAR2(12)

Type of database object. See Table 3-1.

OWNER#

NUMBER

Owner number

LOCK_ELEMENT_ADDR

RAW(4)

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

LOCK_ELEMENT_
NAME

NUMBER

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

PARTITION_NAME

VARCHAR2(30)

NULL for non-partitioned objects

Column	Datatype	Description
FILE#	NUMBER	Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES)
BLOCK#	NUMBER	Block number
STATUS	VARCHAR2(1)	Status of block: FREE = not currently in use XCUR = exclusive SCUR = shared current CR = consistent read READ = being read from disk MREC = in media recovery mode IREC = in instance recovery mode
XNC	NUMBER	Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete but is retained for historical compatibility.
NAME	VARCHAR2(30)	Name of the database object containing the block
KIND	VARCHAR2(12)	Type of database object. See Table 3-1.
OWNER#	NUMBER	Owner number
LOCK_ELEMENT_ADDR	RAW(4)	The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT_ NAME	NUMBER	The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
PARTITION_NAME	VARCHAR2(30)	NULL for non-partitioned objects

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

Table 3-1 Values for the KIND column

Type Number	KIND Value	Type Number	KIND Value
1	INDEX	11	PACKAGE BODY
2	TABLE	12	TRIGGER
3	CLUSTER	13	TYPE
4	VIEW	14	TYPE BODY
5	SYNONYM	19	TABLE PARTITION
6	SEQUENCE	20	INDEX PARTITION
7	PROCEDURE	21	LOB
8	FUNCTION	22	LIBRARY
9	PACKAGE	NULL	UNKNOWN
10	NON-EXISTENT	------	-------

V$CACHE_LOCK

This is a Parallel Server view.

Column Datatype Description

FILE#

NUMBER

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

BLOCK#

NUMBER

Block number

STATUS

VARCHAR2(4)

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

XNC

NUMBER

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

NAME

VARCHAR2(30)

Name of the database object containing the block

KIND

VARCHAR2(12)

Type of database object. See Table 3-1.

OWNER#

NUMBER

Owner number

LOCK_ELEMENT
_ADDR

RAW(4)

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

LOCK_ELEMENT
_NAME

NUMBER

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

FORCED_READS

NUMBER

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

FORCED_WRITES

NUMBER

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

INDX

NUMBER

Platform specific lock manager identifier

CLASS

NUMBER

Platform specific lock manager identifier

Column	Datatype	Description
FILE#	NUMBER	Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES)
BLOCK#	NUMBER	Block number
STATUS	VARCHAR2(4)	Status of block: FREE = not currently in use XCUR = exclusive SCUR = shared current CR = consistent read READ = being read from disk MREC = in media recovery mode IREC = in instance recovery mode
XNC	NUMBER	Number of parallel cache management (PCM) lock conversions due to contention with another instance
NAME	VARCHAR2(30)	Name of the database object containing the block
KIND	VARCHAR2(12)	Type of database object. See Table 3-1.
OWNER#	NUMBER	Owner number
LOCK_ELEMENT _ADDR	RAW(4)	The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT _NAME	NUMBER	The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
FORCED_READS	NUMBER	Number of times the block had to be made re-read from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on this block in lock mode.
FORCED_WRITES	NUMBER	Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the PCM lock on the block in conflicting mode.
INDX	NUMBER	Platform specific lock manager identifier
CLASS	NUMBER	Platform specific lock manager identifier

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

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

V$CIRCUIT

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

Column Datatype Description

CIRCUIT

RAW(4)

Circuit address

DISPATCHER

RAW(4)

Current dispatcher process address

SERVER

RAW(4)

Current server process address

WAITER

RAW(4)

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

SADDR

RAW(4)

Address of session bound to the circuit

STATUS

VARCHAR2

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

QUEUE

VARCHAR2

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

MESSAGE0

NUMBER

Size in bytes of the messages in the first message buffer

MESSAGE1

NUMBER

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

MESSAGES

NUMBER

Total number of messages that have gone through this circuit

BYTES

NUMBER

Total number of bytes that have gone through this circuit

BREAKS

NUMBER

Total number of breaks (interruptions) for this circuit

Column	Datatype	Description
CIRCUIT	RAW(4)	Circuit address
DISPATCHER	RAW(4)	Current dispatcher process address
SERVER	RAW(4)	Current server process address
WAITER	RAW(4)	Address of server process that is waiting for the (currently busy) circuit to become available
SADDR	RAW(4)	Address of session bound to the circuit
STATUS	VARCHAR2	Status of the circuit: BREAK (currently interrupted), EOF (about to be removed), OUTBOUND (an outward link to a remote database), NORMAL (normal circuit into the local database)
QUEUE	VARCHAR2	Queue the circuit is currently on: COMMON (on the common queue, waiting to be picked up by a server process), DISPATCHER (waiting for the dispatcher), SERVER (currently being serviced), OUTBOUND (waiting to establish an outbound connection), NONE (idle circuit)
MESSAGE0	NUMBER	Size in bytes of the messages in the first message buffer
MESSAGE1	NUMBER	Size in bytes of the messages in the second message buffer.
MESSAGES	NUMBER	Total number of messages that have gone through this circuit
BYTES	NUMBER	Total number of bytes that have gone through this circuit
BREAKS	NUMBER	Total number of breaks (interruptions) for this circuit

V$CLASS_PING

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

Column	Datatype	Description
CLASS	NUMBER	Number that represents the block class
X_2_NULL	NUMBER	Number of lock conversions from Exclusive-to-NULL for all blocks of the specified CLASS
X_2_NULL_FORCED_WRITE	NUMBER	Number of forced writes that occur for blocks of the specified CLASS due to Exclusive-to-NULL conversions
X_2_NULL_FORCED_STALE	NUMBER	Number of times a block in the CLASS was made STALE due to Exclusive-to-NULL conversions
X_2_S	NUMBER	Number of lock conversions from Exclusive-to-Shared for all blocks of the specified CLASS
X_2_S_FORCED_WRITE	NUMBER	Number of forced writes that occur for blocks of the specified CLASS due to Exclusive-to-Shared conversions
X_2_SSX	NUMBER	Number of lock conversions from Exclusive-to-Sub Shared Exclusive for all blocks of the specified CLASS
X_2_SSX_FORCED_WRITE	NUMBER	Number of forced writes that occur for blocks of the specified CLASS due to Exclusive-to-Sub Shared Exclusive conversions
S_2_NULL	NUMBER	Number of lock conversions from Shared-to-NULL for all blocks of the specified CLASS
S_2_NULL_FORCED_STALE	NUMBER	Number of times a block in the CLASS was made STALE due to Shared-to-NULL conversions
SS_2_NULL	NUMBER	Number of lock conversions from Sub Shared-to-NULL for all blocks of the specified CLASS
NULL_2_X	NUMBER	Number of lock conversions from NULL-to-Exclusive for all blocks of the specified CLASS
S_2_X	NUMBER	Number of lock conversions from Shared-to-Exclusive for all blocks of the specified CLASS
SSX_2_X	NUMBER	Number of lock conversions from Sub Shared Exclusive-to-Exclusive for all blocks of the specified CLASS
NULL_2_S	NUMBER	Number of lock conversions from NULL-to-Shared for all blocks of the specified CLASS
NULL_2_SS	NUMBER	Number of lock conversions from NULL-to-Sub Shared for all blocks of the specified CLASS

V$COMPATIBILITY

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

Column Datatype Description

TYPE_ID

VARCHAR2(8

Internal feature identifier

RELEASE

VARCHAR2(60)

Release in which that feature appeared

DESCRIPTION

VARCHAR2(64)

Description of the feature

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

Column	Datatype	Description
TYPE_ID	VARCHAR2(8)	Internal feature identifier
RELEASE	VARCHAR2(60)	Release in which that feature appeared. The software must be able to interpret data formats added in that release
UPDATED	VARCHAR2(60)	Release that first used the feature

V$CONTROLFILE

This view lists the names of the control files.

Column Datatype Description

STATUS

VARCHAR2(7)

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

NAME

VARCHAR2(257)

The name of the control file.

V$CONTROLFILE_RECORD_SECTION

This view displays information about the controlfile record sections.

Column Datatype Description

TYPE

VARCHAR2(17)

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

RECORD_SIZE

NUMBER

Record size in bytes

RECORDS_TOTAL

NUMBER

Number of records allocated for the section

RECORDS_USED

NUMBER

Number of records used in the section

FIRST_INDEX

NUMBER

Index (position) of the first record

LAST_INDEX

NUMBER

Index of the last record

LAST_RECID

NUMBER

Record ID of the last record

V$COPY_CORRUPTION

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

Column Datatype Description

RECID

NUMBER

Copy corruption record ID

STAMP

NUMBER

Copy corruption record stamp

COPY_RECID

NUMBER

Datafile copy record ID

COPY_STAMP

NUMBER

Datafile copy record stamp

FILE#

NUMBER

Datafile number

BLOCK#

NUMBER

First block of the corrupted range

BLOCKS

NUMBER

Number of contiguous blocks in the corrupted range

CORRUPTION_CHANGE#

NUMBER

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

MARKED_CORRUPT

VARCHAR2(3)

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

V$CURRENT_BUCKET

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

Column Datatype Description

COUNT

NUMBER

The count

V$DATABASE

This view contains database information from the control file.

Column Datatype Description

NAME

VARCHAR2

Name of the database

CREATED

DATE

Creation date

LOG_MODE

VARCHAR2

Archive log mode: NOARCHIVELOG or ARCHIVELOG

CHECKPOINT_ CHANGE#

NUMBER

Last SCN checkpointed

ARCHIVE_CHANGE#

NUMBER

Last SCN archived

DBID

NUMBER

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

RESETLOGS_CHANGE#

NUMBER

Change# at open resetlogs

RESETLOGS_TIME

DATE

Timestamp of open resetlogs

CONTROLFILE_TYPE

VARCHAR2(7)

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

CONTROLFILE_CREATED

DATE

Controlfile creation timestamp

CONTROLFILE_SEQUENCE#

NUMBER

Controlfile sequence number incremented by controlfile transactions

CONTROLFILE_CHANGE#

NUMBER

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

CONTROLFILE_TIME

DATE

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

OPEN_RESETLOGS

VARCHAR2(11)

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

V$DATAFILE

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

Column Datatype Description

FILE#

NUMBER

File identification number

STATUS

VARCHAR2

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

ENABLED

VARCHAR2(10)

Describes how accessible the file is from SQL. It is one of the values in Table 3-1.

CHECKPOINT
_CHANGE#

NUMBER

SCN at last checkpoint

CHECKPOINT_TIME

DATE

Time stamp of the checkpoint#

UNRECOVERABLE _CHANGE#

NUMBER

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

UNRECOVERABLE
_TIME

DATE

Time stamp of the last unrecoverable change

BYTES

NUMBER

Current size in bytes; 0 if inaccessible

CREATE_BYTES

NUMBER

Size when created, in bytes

NAME

VARCHAR2

Name of the file

CREATION_CHANGE#

NUMBER

Change number at which the datafile was created

CREATION_TIME

DATE

Timestamp of the datafile creation

TS#

NUMBER

Tablespace number

RFILE#

NUMBER

Tablespace relative datafile number

LAST_CHANGE#

NUMBER

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

LAST_TIME

DATE

Timestamp of the last change

OFFLINE_CHANGE#

NUMBER

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

ONLINE_CHANGE#

NUMBER

Online change# of the last offline range

ONLINE_TIME

DATE

Online timestamp of the last offline range

BLOCKS

NUMBER

Current datafile size in blocks; 0 if inaccessible

BLOCK_SIZE

NUMBER

Block size of the datafile

NAME

VARCHAR2(512)

Datafile name

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

Table 3-2 Values for the ENABLED Column

ENABLED Column Value	Description
DISABLED	No SQL access allowed
READ ONLY	No SQL updates allowed
READ WRITE	Full access allowed
UNKNOWN	Should not occur unless the control file is corrupted

V$DATAFILE_COPY

This view displays datafile copy information from the controlfile.

Column Datatype Description

RECID

NUMBER

Datafile copy record ID

STAMP

NUMBER

Datafile copy record stamp

NAME

VARCHAR2(512)

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

TAG

VARCHAR2(32)

Datafile copy tag

FILE#

NUMBER

Absolute datafile number

RFILE#

NUMBER

Tablespace relative datafile number

CREATION_CHANGE#

NUMBER

Datafile creation change#

CREATION_TIME

DATE

Datafile creation timestamp

RESETLOGS_CHANGE#

NUMBER

Resetlogs change# of the datafile when the copy was made

RESETLOGS_TIME

DATE

Resetlogs timestamp of the datafile when the copy was made

CHECKPOINT_CHANGE#

NUMBER

Checkpoint change# of the datafile when the copy was made

CHECKPOINT_TIME

DATE

Checkpoint timestamp of the datafile when the copy was made

ABSOLUTE_FUZZY
_CHANGE#

NUMBER

Highest change seen when the datafile was copied

RECOVERY_FUZZY
_CHANGE#

NUMBER

Highest change written to the file by media recovery

RECOVERY_FUZZY
_TIME

DATE

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

ONLINE_FUZZY

VARCHAR2(3)

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

BACKUP_FUZZY

VARCHAR2(3)

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

MARKED_CORRUPT

NUMBER

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

MEDIA_CORRUPT

NUMBER

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

LOGICALLY_CORRUPT

NUMBER

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

BLOCKS

NUMBER

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

BLOCK_SIZE

NUMBER

Block size of the datafile

OLDEST_OFFLINE
_RANGE

NUMBER

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

COMPLETION_TIME

DATE

Time when the copy was completed

DELETED

VARCHAR2(3)

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

V$DATAFILE_HEADER

This view displays datafile information from the datafile headers.

Column Datatype Description

FILE#

NUMBER

Datafile number (from controlfile)

STATUS

VARCHAR2(7)

ONLINE/OFFLINE (from controlfile)

ERROR

VARCHAR2(18)

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

FORMAT

NUMBER

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

6 - indicates Oracle Version 6

7 - indicates Oracle Version 7

8 - indicates Oracle Version 8

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

RECOVER

VARCHAR2(3)

File needs media recovery YES/NO

FUZZY

VARCHAR2(3)

File is fuzzy YES/NO

CREATION_CHANGE#

NUMBER

Datafile creation change#

CREATION_TIME

DATE

Datafile creation timestamp

TABLESPACE_NAME

VARCHAR2(30)

Tablespace name

TS#

NUMBER

Tablespace number

RFILE#

NUMBER

Tablespace relative datafile number

RESETLOGS_CHANGE#

NUMBER

Resetlogs change#

RESETLOGS_TIME

DATE

Resetlogs timestamp

CHECKPOINT_CHANGE#

NUMBER

Datafile checkpoint change#

CHECKPOINT_TIME

DATE

Datafile checkpoint timestamp

CHECKPOINT_COUNT

NUMBER

Datafile checkpoint count

BYTES

NUMBER

Current datafile size in bytes

BLOCKS

NUMBER

Current datafile size in blocks

NAME

VARCHAR2(512)

Datafile name

V$DBFILE

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

Column Datatype Description

FILE#

NUMBER

File identifier

NAME

VARCHAR2

Name of file

V$DBLINK

This view describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be committed or rolled back before being closed.

Column Datatype Description

DB_LINK

VARCHAR2(128)

Name of the database link

OWNER_ID

NUMBER

Owner of the database link UID

LOGGED_ON

VARCHAR2(3)

Whether the database link is currently logged on

HETEROGENEOUS

VARCHAR2(3)

Whether the database link is heterogeneous

PROTOCOL

VARCHAR2(6)

Communication protocol for the database link

OPEN_CURSORS

NUMBER

Whether there are open cursors for the database link

IN_TRANSACTION

VARCHAR2(3)

Whether the database link is currently in a transaction

UPDATE_SENT

VARCHAR2(3)

Whether there has been an update on the database link

COMMIT_POINT
_STRENGTH

NUMBER

Commit point strength of the transactions on the database link

V$DB_OBJECT_CACHE

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

Column Datatype Description

OWNER

VARCHAR2

Owner of the object

NAME

VARCHAR2

Name of the object

DB_LINK

VARCHAR2

Database link name, if any

NAMESPACE

VARCHAR2

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

TYPE

VARCHAR2

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

SHARABLE_MEM

NUMBER

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

LOADS

NUMBER

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

EXECUTIONS

NUMBER

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

LOCKS

NUMBER

Number of users currently locking this object

PINS

NUMBER

Number of users currently pinning this object

KEPT

VARCHAR2(3)

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

V$DB_PIPES

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

Column Datatype Description

OWNERID

NUMBER

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

NAME

VARCHAR2

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

TYPE

VARCHAR2

PUBLIC or PRIVATE

PIPE_SIZE

NUMBER

The amount of memory the pipe uses

V$DELETED_OBJECT

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

Column Datatype Description

RECID

NUMBER

Deleted object record ID

STAMP

NUMBER

Deleted object record stamp

TYPE

VARCHAR2(13)

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

OBJECT_RECID

NUMBER

Record ID of the deleted object

OBJECT_STAMP

NUMBER

Record timestamp of the deleted object

V$DISPATCHER

This view provides information on the dispatcher processes.

Column Datatype Description

NAME

VARCHAR2

Name of the dispatcher process

NETWORK

VARCHAR2

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

PADDR

RAW(4)

Process address

STATUS

VARCHAR2

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

ACCEPT

VARCHAR2

Whether this dispatcher is accepting new connections: YES, NO

MESSAGES

NUMBER

Number of messages processed by this dispatcher

BYTES

NUMBER

Size in bytes of messages processed by this dispatcher

BREAKS

NUMBER

Number of breaks occurring in this connection

OWNED

NUMBER

Number of circuits owned by this dispatcher

CREATED

NUMBER

Number of circuits created by this dispatcher

IDLE

NUMBER

Total idle time for this dispatcher in hundredths of a second

BUSY

NUMBER

Total busy time for this dispatcher in hundredths of a second

LISTENER

NUMBER

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

V$DISPATCHER_RATE

This view provides rate statistics for the dispatcher processes.

V$DLM_CONVERT_LOCAL

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

Column Datatype Description

INST_ID

NUMBER

ID of the instance

CONVERT_TYPE

VARCHAR2(64)

Conversion types are listed in Table 3-3

AVERAGE
_CONVERT_TIME

NUMBER

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

CONVERT_COUNT

NUMBER

The number of operations.

V$DLM_CONVERT_REMOTE

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

Column Datatype Description

INST_ID

NUMBER

ID of the instance

CONVERT_TYPE

VARCHAR2(64)

Conversion types are listed in Table 3-3

AVERAGE_CONVERT
_TIME

NUMBER

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

CONVERT_COUNT

NUMBER

The number of operations.

Table 3-3 Values for the CONVERT_TYPE column

Conversion Type	Description
NULL -> SS	NULL mode to sub shared mode
NULL -> SX	NULL mode to shared exclusive mode
NULL -> S	NULL mode to shared mode
NULL -> SSX	NULL mode to sub-shared exclusive mode
NULL -> X	NULL mode to exclusive mode
SS -> SX	sub shared mode to shared exclusive mode
SS -> S	sub shared mode to shared mode
SS -> SSX	sub shared mode to sub-shared exclusive mode
SS -> X	sub shared mode to exclusive mode
SX -> S	shared exclusive mode to shared mode
SX -> SSX	shared exclusive mode to sub-shared exclusive mode
SX -> X	shared exclusive mode to exclusive mode
S -> SX	shared mode to shared exclusive mode
S -> SSX	shared mode to sub-shared exclusive mode
S -> X	shared mode to exclusive mode
SSX -> X	sub-shared exclusive mode to exclusive mode

V$DLM_LATCH

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

Column Datatype Description

LATCH_TYPE

VARCHAR2(64)

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

IMM_GETS

NUMBER

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

TTL_GETS

NUMBER

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

Table 3-4 Values for the LATCH_TYPE column

deadlock list	domain lock latch
domain lock table latch	domain table latch
group lock latch	group lock table latch
group table freelist	lock table freelist
log/trace file latch	proc hash list
proc lock list	proc table freelist
rdomain record latch	rdomain table latch
resource hash list	resource scan list
resource structure	resource table freelist
shared comm. latch	stat table latch
sync data latch	timeout list

V$DLM_LOCKS

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

V$DLM_MISC

V$DLM_MISC displays miscellaneous DLM statistics.

Column Datatype Description

STATISTIC#

NUMBER

Statistic number

NAME

VARCHAR2(64)

Name of the statistic

VALUE

NUMBER

Value associated with the statistic

V$ENABLEDPRIVS

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

Column Datatype Description

PRIV_NUMBER

NUMBER

Numeric identifier of enabled privileges

V$ENQUEUE_LOCK

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

Column Datatype Description

ADDR

RAW(4)

Address of lock state object

KADDR

RAW(4)

Address of lock

SID

NUMBER

Identifier for session holding or acquiring the lock

TYPE

VARCHAR2(2)

Type of lock. For a list of user and system types that can have locks, see Table 3-4, "Values for the LATCH_TYPE column".

ID1

NUMBER

Lock identifier #1 (depends on type)

ID2

NUMBER

Lock identifier #2 (depends on type)

LMODE

NUMBER

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

REQUEST

NUMBER

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

CTIME

NUMBER

Time since current mode was granted

BLOCK

NUMBER

The lock is blocking another lock

V$EVENT_NAME

This view contains information about wait events.

Column Datatype Description

EVENT#

NUMBER

The number of the wait event

NAME

VARCHAR2(64)

The name of the wait event

PARAMETER1

VARCHAR2(64)

The description of the first parameter for the wait event

PARAMETER2

VARCHAR2(64)

The description of the second parameter for the wait event

PARAMETER3

VARCHAR2(64)

The description of the third parameter for the wait event

V$EXECUTION

This view displays information on parallel query execution.

Column Datatype Description

PID

NUMBER

Session ID

DEPTH

NUMBER

The depth

FUNCTION

VARCHAR2(10)

Session serial number

TYPE

VARCHAR2(7)

Name of the OBJECT_NODE in plan table

NVALS

NUMBER

Elapsed time for OBJECT_NODE

VAL1

NUMBER

The value for number 1

VAL2

NUMBER

The value for number 2

SEQH

NUMBER

A sequence

SEQL

NUMBER

A sequence

V$EXECUTION_LOCATION

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

Column Datatype Description

SID

NUMBER

Session ID

SERIAL#

NUMBER

Session serial number

OBJECT_NODE

VARCHAR2(20)

Name of the OBJECT_NODE in plan table

ELAPSED_TIME

NUMBER

Elapsed time for OBJECT_NODE

V$FALSE_PING

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

Column Datatype Description

FILE#

NUMBER

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

BLOCK#

NUMBER

Block number

STATUS

VARCHAR2(1)

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

XNC

NUMBER

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

FORCED_READS

NUMBER

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

FORCED_WRITES

NUMBER

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

NAME

VARCHAR2(30)

Name of the database object containing the block

PARTITION_NAME

VARCHAR2

NULL for non-partitioned objects

KIND

VARCHAR2(12)

Type of database object. See Table 3-1.

OWNER#

NUMBER

Owner number

LOCK_ELEMENT_ADDR

RAW(4)

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

LOCK_ELEMENT_NAME

NUMBER

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

LOCK_ELEMENT_CLASS

NUMBER

The lock element class

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

V$FILE_PING

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

V$FILESTAT

This view contains information about file read/write statistics.

Column Datatype Description

FILE#

NUMBER

Number of the file

PHYRDS

NUMBER

Number of physical reads done

PHYWRTS

NUMBER

Number of times DBWR is required to write

PHYBLKRD

NUMBER

Number of physical blocks read

PHYBLKWRT

NUMBER

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

READTIM

NUMBER

Time (in hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

WRITETIM

NUMBER

Time (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

AVGIOTIM

NUMBER

The average time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

LSTIOTIM

NUMBER

The time (in hundredths of a second) spent doing the last I/O, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

MINIOTIM

NUMBER

The minimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

MAXIOWTM

NUMBER

The maximum time (in hundredths of a second) spent doing a single write, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

MAXIORTM

NUMBER

The maximum time (in hundredths of a second) spent doing a single read, if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

V$FIXED_TABLE

This view displays all dynamic performance tables, views, and derived tables in the database. Some V$ tables refer to real tables and are therefore not listed.

Column Datatype Description

NAME

VARCHAR2(30)

Name of the object

OBJECT_ID

NUMBER

Identifier of the fixed object

TYPE

VARCHAR2(5)

Object type: TABLE, VIEW

TABLE_NUM

NUMBER

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

V$FIXED_VIEW_DEFINITION

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

Column Datatype Description

VIEW_NAME

VARCHAR2(30)

The name of the fixed view

VIEW_DEFINITION

VARCHAR2(2000)

The definition of the fixed view

V$GLOBAL_TRANSACTION

This view displays information on the currently active global transactions.

Column Datatype Description

FORMATID

NUMBER

Format identifier of the global transaction

GLOBALID

RAW(64)

Global transaction identifier of the global transaction

BRANCHID

RAW(64)

Branch qualifier of the global transaction

BRANCHES

NUMBER

Total number of branches in the global transaction

REFCOUNT

NUMBER

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

PREPARECOUNT

NUMBER

Number of branches of the global transaction that have prepared

STATE

VARCHAR2(18)

State of the branch of the global transaction

FLAGS

NUMBER

The numerical representation of the state

COUPLING

VARCHAR2(15)

Whether the branches are loosely coupled or tightly coupled

V$INDEXED_FIXED_COLUMN

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

Column Datatype Description

TABLE_NAME

VARCHAR2(30)

The name of the dynamic performance table that is indexed

INDEX_NUMBER

NUMBER

Number that distinguishes to which index a column belongs

COLUMN_NAME

VARCHAR2(30)

Name of the column that is being indexed

COLUMN_POSITION

NUMBER

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

V$INSTANCE

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

Column Datatype Description

INSTANCE_NUMBER

NUMBER

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

See "INSTANCE_NUMBER" on page 1-51.

INSTANCE_NAME

VARCHAR2(16)

Instance name.

HOST_NAME

VARCHAR2(64)

Name of the host machine

VERSION

VARCHAR2(17)

RDBMS version

STARTUP_TIME

DATE

Time when instance was started up

STATUS

VARCHAR2(7)

STARTED/MOUNTED/OPEN

STARTED after startup nomount

MOUNTED after startup mount or alter database close

OPEN after startup or after database open

PARALLEL

VARCHAR2(3)

YES/NO in parallel server mode

THREAD#

NUMBER

Redo thread opened by the instance

ARCHIVER

VARCHAR2(7)

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

LOG_SWITCH_WAIT

VARCHAR2(11)

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

LOGINS

VARCHAR2(10)

ALLOWED/RESTRICTED

SHUTDOWN_PENDING

VARCHAR2(3)

YES/NO

V$LATCH

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

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

V$LATCHHOLDER

This view contains information about the current latch holders.

Column Datatype Description

PID

NUMBER

Identifier of process holding the latch

SID

NUMBER

Identifier of the session that owns the latch

LADDR

RAW(4)

Latch address

NAME

VARCHAR2

Name of latch being held

V$LATCHNAME

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

Column Datatype Description

LATCH#

NUMBER

Latch number

NAME

VARCHAR2(64)

Latch name

V$LATCH_CHILDREN

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

Column Datatype Description

ADDR

RAW(4)

Address of latch object

LATCH#

NUMBER

Latch number for a parent latch

CHILD#

NUMBER

Child number of a parent latch shown in LATCH#

LEVEL#

NUMBER

Latch level

NAME

VARCHAR2(64)

Latch name

GETS

NUMBER

Number of times obtained a wait

MISSES

NUMBER

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

SLEEPS

NUMBER

Number of times slept when wanted a wait

IMMEDIATE_GETS

NUMBER

Number of times obtained without a wait

IMMEDIATE_MISSES

NUMBER

Number of time failed to get without a wait

WAITERS_WOKEN

NUMBER

How many times a wait was awakened

WAITS_HOLDING
_LATCH

NUMBER

Number of waits while holding a different latch

SPIN_GETS

NUMBER

Gets that missed first try but succeeded on spin

SLEEPn

NUMBER

Waits that slept n times

V$LATCH_MISSES

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

Column Datatype Description

PARENT_NAME

VARCHAR2

Latch name of a parent latch

WHERE

VARCHAR2

Location that attempted to acquire the latch

NWFAIL_COUNT

NUMBER

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

SLEEP_COUNT

NUMBER

Number of times that acquisition attempts caused sleeps

V$LATCH_PARENT

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

V$LIBRARYCACHE

This view contains statistics about library cache performance and activity.

V$LICENSE

This view contains information about license limits.

Column Datatype Description

SESSIONS_MAX

NUMBER

Maximum number of concurrent user sessions allowed for the instance

SESSIONS_WARNING

NUMBER

Warning limit for concurrent user sessions for the instance

SESSIONS_CURRENT

NUMBER

Current number of concurrent user sessions

SESSIONS
_HIGHWATER

NUMBER

Highest number of concurrent user sessions since the instance started

USERS_MAX

NUMBER

Maximum number of named users allowed for the database

V$LOADCSTAT

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

Column Datatype Description

READ

NUMBER

Number of records read

REJECTED

NUMBER

Number of records rejected

TDISCARD

NUMBER

Total number of discards during the load

NDISCARD

NUMBER

Number of discards from the current file

V$LOADTSTAT

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

Column Datatype Description

LOADED

NUMBER

Number of records loaded

REJECTED

NUMBER

Number of records rejected

FAILWHEN

NUMBER

Number of records that failed to meet any WHEN clause

ALLNULL

NUMBER

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

LEFT2SKIP

NUMBER

Number of records yet to skip during a continued load

PTNLOADED

NUMBER

Number of records loaded PTN

V$LOCK

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

Column Datatype Description

ADDR

RAW(4)

Address of lock state object

KADDR

RAW(4)

Address of lock

SID

NUMBER

Identifier for session holding or acquiring the lock

TYPE

VARCHAR2(2)

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

ID1

NUMBER

Lock identifier #1 (depends on type)

ID2

NUMBER

Lock identifier #2 (depends on type)

LMODE

NUMBER

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

REQUEST

NUMBER

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

CTIME

NUMBER

Time since current mode was granted

BLOCK

NUMBER

The lock is blocking another lock

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

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

User Type	Description
TM	DML enqueue
TX	Transaction enqueue
UL	User supplied

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

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

System Type	Description
BL	Buffer hash table instance
CF	Control file schema global enqueue
CI	Cross-instance function invocation instance
CU	Cursor bind
DF	Data file instance
DL	Direct loader parallel index create
DM	Mount/startup db primary/secondary instance
DR	Distributed recovery process
DX	Distributed transaction entry
FS	File set
HW	Space management operations on a specific segment
IN	Instance number
IR	Instance recovery serialization global enqueue
IS	Instance state
IV	Library cache invalidation instance
JQ	Job queue
KK	Thread kick
LA .. LP	Library cache lock instance lock (A..P = namespace)
MM	Mount definition global enqueue
MR	Media recovery
NA..NZ	Library cache pin instance (A..Z = namespace)
PF	Password File
PI, PS	Parallel operation
PR	Process startup
QA..QZ	Row cache instance (A..Z = cache)
RT	Redo thread global enqueue
SC	System commit number instance
SM	SMON
SN	Sequence number instance
SQ	Sequence number enqueue
SS	Sort segment
ST	Space transaction enqueue
SV	Sequence number value
TA	Generic enqueue
TS	Temporary segment enqueue (ID2=0)
TS	New block allocation enqueue (ID2=1)
TT	Temporary table enqueue
UN	User name
US	Undo segment DDL
WL	Being-written redo log instance

V$LOCK_ACTIVITY

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

Column Datatype Description

FROM_VAL

VARCHAR2(4)

PCM lock initial state:
NULL
S
X
SSX

TO_VAL

VARCHAR2(4)

PCM lock initial state:
NULL
S
X
SSX

ACTION_VAL

VARCHAR2(51)

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

COUNTER

NUMBER

Number of times the lock operation executed

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

V$LOCK_ELEMENT

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

Column Datatype Description

LOCK_ELEMENT_ ADDR

RAW(4)

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

LOCK_ELEMENT_NAME

NUMBER

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

INDX

NUMBER

Platform specific lock manager identifier

CLASS

NUMBER

Platform specific lock manager identifier

MODE_HELD

NUMBER

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

BLOCK_COUNT

NUMBER

Number of blocks covered by PCM lock

RELEASING

NUMBER

Non-zero if PCM lock is being downgraded

ACQUIRING

NUMBER

Non-zero if PCM lock is being upgraded

INVALID

NUMBER

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

FLAGS

NUMBER

Process level flags for the LE

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

V$LOCKED_OBJECT

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

Column Datatype Description

XIDUSN

NUMBER

Undo segment number

XIDSLOT

NUMBER

Slot number

XIDSQN

NUMBER

Sequence number

OBJECT_ID

NUMBER

Object ID being locked

SESSION_ID

NUMBER

Session ID

ORACLE_USERNAME

VARCHAR2(30)

Oracle user name

OS_USER_NAME

VARCHAR2(15)

OS user name

PROCESS

VARCHAR2(9)

OS process ID

LOCKED_MODE

NUMBER

Lock mode

V$LOCKS_WITH_COLLISIONS

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

Column Datatype Description

LOCK_ELEMENT_ ADDR

RAW(4)

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

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

V$LOG

This view contains log file information from the control files.

Column Datatype Description

GROUP#

NUMBER

Log group number

THREAD#

NUMBER

Log thread number

SEQUENCE#

NUMBER

Log sequence number

BYTES

NUMBER

Size of the log in bytes

MEMBERS

NUMBER

Number of members in the log group

ARCHIVED

VARCHAR2

Archive status: YES, NO

STATUS

VARCHAR2(16)

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

FIRST_CHANGE#

NUMBER

Lowest SCN in the log

FIRST_TIME

DATE

Time of first SCN in the log

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

Table 3-7 Values for the STATUS Column

STATUS	Meaning
UNUSED	Indicates the online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT	Indicates this is the current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE	Indicates the log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived.
CLEARING	Indicates the log is being recreated as an empty log after an ALTER DATABASE CLEAR LOGFILE command. After the log is cleared, the status changes to UNUSED.
CLEARING _CURRENT	Indicates that the current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE	Indicates the log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

V$LOGFILE

This view contains information about redo log files.

Column Datatype Description

GROUP#

NUMBER

Redo log group identifier number

STATUS

VARCHAR2

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

MEMBER

VARCHAR2

Redo log member name

V$LOGHIST

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

Column Datatype Description

THREAD#

NUMBER

Log thread number

SEQUENCE#

NUMBER

Log sequence number

FIRST_CHANGE#

NUMBER

Lowest SCN in the log

FIRST_TIME

DATE

Time of first SCN in the log

SWITCH_CHANGE#

NUMBER

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

V$LOG_HISTORY

This view contains log history information from the control file.

Column Datatype Description

THREAD#

NUMBER

Thread number of the archived log

SEQUENCE#

NUMBER

Sequence number of the archived log

FIRST_TIME

DATE

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

FIRST_CHANGE#

NUMBER

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

NEXT_CHANGE#

NUMBER

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

RECID

NUMBER

Controlfile record ID

STAMP

NUMBER

Controlfile record stamp

V$MLS_PARAMETERS

This is a Trusted Oracle Server view that lists Trusted Oracle Server-specific initialization parameters. For more information, see your Trusted Oracle documentation.

V$MTS

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

Column Datatype Description

MAXIMUM
_CONNECTIONS

NUMBER

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

SERVERS
_STARTED

NUMBER

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

SERVERS
_TERMINATED

NUMBER

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

SERVERS
_HIGHWATER

NUMBER

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

V$MYSTAT

This view contains statistics on the current session.

Column Datatype Description

SID

NUMBER

The ID of the current session

STATISTIC#

NUMBER

The number of the statistic

VALUE

NUMBER

The value of the statistic

V$NLS_PARAMETERS

This view contains current values of NLS parameters.

Column Datatype Description

PARAMETER

VARCHAR2

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

VALUE

VARCHAR2

NLS parameter value

V$NLS_VALID_VALUES

This view lists all valid values for NLS parameters.

Column Datatype Description

PARAMETER

VARCHAR2(64)

Parameter name:
LANGUAGE
SORT
TERRITORY
CHARACTERSET

VALUE

VARCHAR2(64)

NLS parameter value

V$OBJECT_DEPENDENCY

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

Column Datatype Description

FROM_ADDRESS

RAW(4)

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

FROM_HASH

NUMBER

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

TO_OWNER

VARCHAR2(64)

The owner of the object that is depended on

TO_NAME

VARCHAR2(1000)

The name of the object that is depended on

TO_ADDRESS

RAW(4)

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

TO_HASH

NUMBER

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

TO_TYPE

NUMBER

The type of the object that is depended on

V$OFFLINE_RANGE

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

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

Column Datatype Description

RECID

NUMBER

Record ID

STAMP

NUMBER

Record stamp

FILE#

NUMBER

Datafile number

OFFLINE_CHANGE#

NUMBER

SCN at which offlined

ONLINE_CHANGE#

NUMBER

SCN at which onlined

ONLINE_TIME

DATE

Time of offline SCN

V$OPEN_CURSOR

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

Column Datatype Description

SADDR

RAW

Session address

SID

NUMBER

Session identifier

USER_NAME

VARCHAR2(30)

User that is logged in to the session

ADDRESS

RAW

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

HASH_VALUE

NUMBER

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

SQL_TEXT

VARCHAR2(60)

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

V$OPTION

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

Column Datatype Description

PARAMETER

VARCHAR2(64)

The name of the option

VALUE

VARCHAR2(64)

TRUE if the option is installed

V$PARAMETER

This view lists information about initialization parameters.

Column Datatype Description

NUM

NUMBER

Parameter number

NAME

VARCHAR2(64)

Parameter name

TYPE

NUMBER

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

VALUE

VARCHAR2(512)

Parameter value

ISDEFAULT

VARCHAR2(9)

Whether the parameter value is the default

ISSES_MODIFIABLE

VARCHAR2(5)

TRUE = the parameter can be changed with ALTER SESSION

FALSE= the parameter cannot be changed with ALTER SESSION

ISSYS_MODIFIABLE

VARCHAR2(9)

IMMEDIATE = the parameter can be changed with ALTER SYSTEM

DEFERRED=the parameter cannot be changed until the next session

FALSE= the parameter cannot be changed with ALTER SYSTEM

ISMODIFIED

VARCHAR2(10)

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

ISADJUSTED

VARCHAR2(5)

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

DESCRIPTION

VARCHAR2(64)

A descriptive comment about the parameter

V$PING

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

Column Datatype Description

FILE#

NUMBER

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

BLOCK#

NUMBER

Block number

CLASS#

NUMBER

Class number

STATUS

VARCHAR2(4)

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

XNC

NUMBER

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

FORCED_READS

NUMBER

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

FORCED_WRITES

NUMBER

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

NAME

VARCHAR2(30)

Name of the database object containing the block

PARTITION_NAME

VARCHAR2(30)

NULL for non-partitioned objects

KIND

VARCHAR2(15)

Type of database object. See Table 3-1.

OWNER#

NUMBER

Owner number

LOCK_ELEMENT
_ ADDR

RAW(4)

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

LOCK_ELEMENT
_NAME

NUMBER

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

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

V$PQ_SESSTAT

This view lists session statistics for parallel queries.

Column Datatype Description

STATISTIC

VARCHAR2(30)

Name of the statistic. See Table 3-6

LAST_QUERY

NUMBER

The value of the statistic for the last operation

SESSION_TOTAL

NUMBER

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

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

Table 3-8 Names of Statistics in the STATISTIC Column

Statistic (Fixed Row)	Description
Queries Parallelized	Number of queries that were run in parallel
DML Parallelized	Number of DML operations that were run in parallel.
DFO Trees	Number of executed DFO trees
Server Threads	Total number of parallel servers used
Allocation Height	Requested number of servers per instance
Allocation Width	Requested number of instances
Local Msgs Sent	Number of local (intra-instance) messages sent
Distr Msgs Sent	Number of remote (inter-instance) messages sent
Local Msgs Recv'd	Number of local (intra-instance) messages received
Distr Msgs Recv'd	Number of remote (inter-instance) messages received

V$PQ_SLAVE

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

Column Datatype Description

SLAVE_NAME

VARCHAR2(4)

Name of the parallel query server

STATUS

VARCHAR2(4)

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

SESSIONS

NUMBER

The number of sessions that have used this parallel query server

IDLE_TIME_CUR

NUMBER

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

BUSY_TIME_CUR

NUMBER

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

CPU_SECS_CUR

NUMBER

The amount of CPU time spent on the current session

MSGS_SENT_CUR

NUMBER

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

MSGS_RCVD_CUR

NUMBER

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

IDLE_TIME_TOTAL

NUMBER

The total amount of time this query server has been idle

BUSY_TIME_TOTAL

NUMBER

The total amount of time this query server has been active

CPU_SECS_TOTAL

NUMBER

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

MSGS_SENT_TOTAL

NUMBER

The total number of messages this query server has sent

MSGS_RCVD_TOTAL

NUMBER

The total number of messages this query server has received

V$PQ_SYSSTAT

This view lists system statistics for parallel queries.

Column Datatype Description

STATISTIC

VARCHAR2(30)

Name of the statistic. See Table 3-9

VALUE

NUMBER

The value of the statistic

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

Table 3-9 Names of Statistics in the STATISTICS Column

Statistic (Fixed Row)	Description
Servers Busy	Number of currently busy servers on this instance
Servers Idle	Number of currently idle servers on this instance
Servers Highwater	Number of active servers on this instance that have partaken in >= 1 operation so far
Server Sessions	Total number of operations executed in all servers on this instance
Servers Started	Total number of servers started on this instance
Servers Shutdown	Total number of servers shutdown on this instance
Servers Cleaned Up	Total number of servers on this instance cleaned up due to process death
Queries Initiated	Total number of parallel queries initiated on this instance
DML Initiated	Total number of parallel DML operations that were initiated
DFO Trees	Total number of DFO trees executed on this instance
Local Msgs Sent	Total number of local (intra-instance) messages sent on this instance
Distr Msgs Sent	Total number of remote (inter-instance) messages sent on this instance
Local Msgs Recv'd	Total number of remote (inter-instance) messages received on this instance
Distr Msgs Recv'd	Total number of remote (inter-instance) messages received on this instance

V$PQ_TQSTAT

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

Column Datatype Description

DFO_NUMBER

NUMBER

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

TQ_ID

NUMBER

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

SERVER_TYPE

VARCHAR2(10)

The role in table queue - producer/consumer/ranger

NUM_ROWS

NUMBER

The number of rows produced/consumed

BYTES

NUMBER

The number of bytes produced/consumed

OPEN_TIME

NUMBER

Time (secs) the table queue remained open

AVG_LATENCY

NUMBER

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

WAITS

NUMBER

The number of waits encountered during dequeue

TIMEOUTS

NUMBER

The number of timeouts when waiting for a message

PROCESS

VARCHAR2(10)

Process ID

INSTANCE

NUMBER

Instance ID

V$PROCESS

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

Column Datatype Description

ADDR

RAW(4)

Address of process state object

PID

NUMBER

Oracle process identifier

SPID

VARCHAR2

Operating system process identifier

USERNAME

VARCHAR2

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

SERIAL#

NUMBER

Process serial number

TERMINAL

VARCHAR2

Operating system terminal identifier

PROGRAM

VARCHAR2

Program in progress

BACKGROUND

VARCHAR2

1 for a background process; NULL for a normal process

LATCHWAIT

VARCHAR2

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

LATCHSPIN

VARCHAR2

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

V$PWFILE_USERS

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

Column Datatype Description

USERNAME

VARCHAR2(30)

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

SYSDBA

VARCHAR2(5)

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

SYSOPER

VARCHAR2(5)

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

V$QUEUE

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

Column Datatype Description

PADDR

RAW(4)

Address of the process that owns the queue

TYPE

VARCHAR2

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

QUEUED

NUMBER

Number of items in the queue

WAIT

NUMBER

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

TOTALQ

NUMBER

Total number of items that have ever been in the queue

V$RECENT_BUCKET

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

Column Datatype Description

COUNT

NUMBER

The count

V$RECOVER_FILE

This view displays the status of files needing media recovery.

Column Datatype Description

FILE#

NUMBER

File identifier number

ONLINE

VARCHAR2

Online status: ONLINE, OFFLINE

ERROR

VARCHAR2

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

CHANGE#

NUMBER

SCN where recovery must start

TIME

DATE

Time of SCN when recovery must start

V$RECOVERY_FILE_STATUS

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

Column Datatype Description

FILENUM

NUMBER

The number of the file being recovered

FILENAME

VARCHAR2(257)

The filename of the datafile being recovered

STATUS

VARCHAR2(13)

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

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

V$RECOVERY_LOG

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

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

Column Datatype Description

THREAD#

NUMBER

Thread number of the archived log

SEQUENCE#

NUMBER

Sequence number of the archived log

TIME

VARCHAR2

Time of first entry (lowest SCN) in the log

ARCHIVE_NAME

VARCHAR2

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

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

V$RECOVERY_PROGRESS

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

V$RECOVERY_PROGRESS is a subview of V$SESSION_LONGOPS.

Column Datatype Description

TYPE

VARCHAR2(64)

The type of recovery operation being performed

ITEM

VARCHAR2(11)

The item being measured

SOFAR

NUMBER

The amount of work done so far

TOTAL

NUMBER

The total amount of work expected

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

V$RECOVERY_STATUS

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

Column Datatype Description

RECOVERY_CHECKPOINT

DATE

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

THREAD

NUMBER

The number of the redo thread currently being processed.

SEQUENCE_NEEDED

NUMBER

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

SCN_NEEDED

VARCHAR2(16)

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

TIME_NEEDED

DATE

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

PREVIOUS_LOG_NAME

VARCHAR2(257)

The filename of the log.

PREVIOUS_LOG_STATUS

VARCHAR2(13)

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

REASON

VARCHAR2(13)

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

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

V$REQDIST

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

Column Datatype Description

BUCKET

NUMBER

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

COUNT

NUMBER

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

V$RESOURCE

This view contains resource name and address information.

Column Datatype Description

ADDR

RAW(4)

Address of resource object

TYPE

VARCHAR2

Resource type. The resource types are listed in Table 3-3 and Table 3-4

ID1

NUMBER

Resource identifier #1

ID2

NUMBER

Resource identifier #2

V$RESOURCE_LIMIT

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

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

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

Column Datatype Description

RESOURCE_NAME

VARCHAR2(30)

Name of the resource (see Table 3-10)

CURRENT
_UTILIZATION

NUMBER

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

MAX_UTILIZATION

NUMBER

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

INITIAL_ALLOCATION

VARCHAR2(10)

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

LIMIT_VALUE

VARCHAR2(10)

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

Table 3-10 Values for RESOURCE_NAME column

Resource Name	Corresponds to this Initialization Parameter
DISTRIBUTED_TRANSACTIONS	DISTRIBUTED_TRANSACTIONS For more information on this parameter, see "DISTRIBUTED_TRANSACTIONS" on page 1-39.
DML_LOCKS	DML_LOCKS For more information on this parameter see "DML_LOCKS" on page 1-40.
ENQUEUE_LOCKS	This value is computed by Oracle. Use the V$ENQUEUE_LOCK view (described on page 3 - 37) to obtain more information about the enqueue locks.
ENQUEUE_RESOURCES	ENQUEUE_RESOURCES For more information on this parameter see "ENQUEUE_RESOURCES" on page 1-41.
LM_PROCESSES	LM_PROCS For more information on this parameter see "LM_PROCS" on page 1-56.
LM_RESOURCES	LM_RESS For more information on this parameter see "LM_RESS" on page 1-57.
LM_LOCKS	LM_LOCKS For more information on this parameter see "LM_LOCKS" on page 1-55.
MTS_MAX_SERVERS	MTS_MAX_SERVERS For more information on this parameter see "MTS_MAX_SERVERS" on page 1-74.
PARALLEL_SLAVES	PARALLEL_MAX_SERVERS For more information on this parameter, see "PARALLEL_MAX_SERVERS" on page 1-94.
PROCESSES	PROCESSES For more information on this parameter, see "PROCESSES" on page 1-100.
ROLLBACK_SEGMENTS	MAX_ROLLBACK_SEGMENTS For more information on this parameter, see "MAX_ROLLBACK_SEGMENTS" on page 1-69.
SESSIONS	SESSIONS For more information on this parameter, see "SESSIONS" on page 1-110.
SORT_SEGMENT_LOCKS	This value is computed by Oracle
TEMPORARY_LOCKS	This value is computed by Oracle
TRANSACTIONS	TRANSACTIONS For more information on this parameter, see "TRANSACTIONS" on page 1-122.

V$ROLLNAME

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

Column Datatype Description

USN

NUMBER

Rollback (undo) segment number

NAME

VARCHAR2

Rollback segment name

V$ROLLSTAT

This view contains rollback segment statistics.

V$ROWCACHE

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

Column Datatype Description

CACHE#

NUMBER

Row cache ID number

TYPE

VARCHAR2

Parent or subordinate row cache type

SUBORDINATE#

NUMBER

Subordinate set number

PARAMETER

VARCHAR2

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

COUNT

NUMBER

Total number of entries in the cache

USAGE

NUMBER

Number of cache entries that contain valid data

FIXED

NUMBER

Number of fixed entries in the cache

GETS

NUMBER

Total number of requests for information on the data object

GETMISSES

NUMBER

Number of data requests resulting in cache misses

SCANS

NUMBER

Number of scan requests

SCANMISSES

NUMBER

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

SCANCOMPLETES

NUMBER

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

MODIFICATIONS

NUMBER

Number of inserts, updates, and deletions

FLUSHES

NUMBER

Number of times flushed to disk

V$SESSION

This view lists session information for each current session.

Column Datatype Description

SADDR

RAW(4)

Session address

SID

NUMBER

Session identifier

SERIAL#

NUMBER

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

AUDSID

NUMBER

Auditing session ID

PADDR

RAW(4)

Address of the process that owns this session

USER#

NUMBER

Oracle user identifier

USERNAME

VARCHAR2(30)

Oracle username

COMMAND

NUMBER

Command in progress (last statement parsed); for a list of values, see Table 3-8

OWNERID

NUMBER

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

For operations using Parallel Slaves, interpret this value as a 4Byte value. The low-order 2Bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator.

TADDR

VARCHAR2(8)

Address of transaction state object

LOCKWAIT

VARCHAR2(8)

Address of lock waiting for; NULL if none

STATUS

VARCHAR2(8)

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

SERVER

VARCHAR2(9)

Server type: DEDICATED, SHARED, PSEUDO, NONE

SCHEMA#

NUMBER

Schema user identifier

SCHEMANAME

VARCHAR2(30)

Schema user name

OSUSER

VARCHAR2(15)

Operating system client user name

PROCESS

VARCHAR2(9)

Operating system client process ID

MACHINE

VARCHAR2(64)

Operating system machine name

TERMINAL

VARCHAR2(10)

Operating system terminal name

PROGRAM

VARCHAR2(48)

Operating system program name

TYPE

VARCHAR2(10)

Session type

SQL_ADDRESS

RAW(4)

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

SQL_HASH_VALUE

NUMBER

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

MODULE

VARCHAR2(48)

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

MODULE_HASH

NUMBER

The hash value of the above MODULE

ACTION

VARCHAR2(32)

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

ACTION_HASH

NUMBER

The hash value of the above action name

CLIENT_INFO

VARCHAR2(64)

Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure.

FIXED_TABLE
_SEQUENCE

NUMBER

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

ROW_WAIT_OBJ#

NUMBER

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

ROW_WAIT_FILE#

NUMBER

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

ROW_WAIT
_BLOCK#

NUMBER

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

ROW_WAIT_ROW#

NUMBER

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

LOGON_TIME

DATE

Time of logon.

LAST_CALL_ET

NUMBER

The last call

PDML_ENABLED

VARCHAR2(3)

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

FAILOVER_TYPE

VARCHAR2(10)

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

FAILOVER
_METHOD

VARCHAR2(3)

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

FAILED_OVER

VARCHAR2(13)

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

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

Table 3-11 Command Numbers for the COMMAND Column

Command Number	Command
0	No command in progress. Occurs when process is in a transitory state, usually when terminating.
1	CREATE TABLE
2	INSERT
3	SELECT
4	CREATE CLUSTER
5	ALTER CLUSTER
6	UPDATE
7	DELETE
8	DROP CLUSTER
9	CREATE INDEX
10	DROP INDEX
11	ALTER INDEX
12	DROP TABLE
13	CREATE SEQUENCE
14	ALTER SEQUENCE
15	ALTER TABLE
16	DROP SEQUENCE
17	GRANT
18	REVOKE
19	CREATE SYNONYM
20	DROP SYNONYM
21	CREATE VIEW
22	DROP VIEW
23	VALIDATE INDEX
24	CREATE PROCEDURE
25	ALTER PROCEDURE
26	LOCK TABLE
27	NO OPERATION
28	RENAME
29	COMMENT
30	AUDIT
31	NOAUDIT
32	CREATE DATABASE LINK
33	DROP DATABASE LINK
34	CREATE DATABASE
35	ALTER DATABASE
36	CREATE ROLLBACK SEGMENT
37	ALTER ROLLBACK SEGMENT
38	DROP ROLLBACK SEGMENT
39	CREATE TABLESPACE
40	ALTER TABLESPACE
41	DROP TABLESPACE
42	ALTER SESSION
43	ALTER USER
44	COMMIT
45	ROLLBACK
46	SAVEPOINT
47	PL/SQL EXECUTE
48	SET TRANSACTION
49	ALTER SYSTEM SWITCH LOG
50	EXPLAIN
51	CREATE USER
52	CREATE ROLE
53	DROP USER
54	DROP ROLE
55	SET ROLE
56	CREATE SCHEMA
57	CREATE CONTROL FILE
58	ALTER TRACING
59	CREATE TRIGGER
60	ALTER TRIGGER
61	DROP TRIGGER
62	ANALYZE TABLE
63	ANALYZE INDEX
64	ANALYZE CLUSTER
65	CREATE PROFILE
66	DROP PROFILE
67	ALTER PROFILE
68	DROP PROCEDURE
69	DROP PROCEDURE
70	ALTER RESOURCE COST
71	CREATE SNAPSHOT LOG
72	ALTER SNAPSHOT LOG
73	DROP SNAPSHOT LOG
74	CREATE SNAPSHOT
75	ALTER SNAPSHOT
76	DROP SNAPSHOT
79	ALTER ROLE
85	TRUNCATE TABLE
86	TRUNCATE CLUSTER
88	ALTER VIEW
91	CREATE FUNCTION
92	ALTER FUNCTION
93	DROP FUNCTION
94	CREATE PACKAGE
95	ALTER PACKAGE
96	DROP PACKAGE
97	CREATE PACKAGE BODY
98	ALTER PACKAGE BODY
99	DROP PACKAGE BODY

V$SESSION_CONNECT_INFO

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

Column Datatype Description

SID

NUMBER

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

AUTHENTICATION
_TYPE

VARCHAR2(15)

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

OSUSER

VARCHAR2(30)

The external username for this database user

NETWORK
_SERVICE_BANNER

VARCHAR2(2000)

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

V$SESSION_CURSOR_CACHE

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

Column Datatype Description

MAXIMUM

NUMBER

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

COUNT

NUMBER

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

OPENED_ONCE

NUMBER

Number of cursors opened at least once

OPEN

NUMBER

Current number of open cursors

OPENS

NUMBER

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

HITS

NUMBER

Cumulative total of cursor open hits

HIT_RATIO

NUMBER

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

V$SESSION_EVENT

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

Column Datatype Description

SID

NUMBER

The ID of the session

EVENT

VARCHAR2(64)

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

TOTAL_WAITS

NUMBER

The total number of waits for this event by this session

TOTAL_TIMEOUTS

NUMBER

The total number of timeouts for this event by this session

TIME_WAITED

NUMBER

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

AVERAGE_WAIT

NUMBER

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

MAX_WAIT

NUMBER

The maximum time (in hundredths of a second) waited for this event by this session

V$SESSION_LONGOPS

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

hash cluster creations
backup operations

recovery operations

Column Datatype Description

SID

NUMBER

Session identifier

SERIAL#

NUMBER

Session serial number

UPDATE_COUNT

NUMBER

The updated count

COMPNAM

VARCHAR2(30)

The component name

OBJID

NUMBER

If present, displays the dictionary object ID on which the operation is being done

CONTEXT

NUMBER

A context specific to the component

STEPID

NUMBER

A number assigned by the component for the execution step

MSG

VARCHAR2(512)

Text describing work done so far and total anticipated work on an object

STEPSOFAR

NUMBER

Units of work done so far in the step

STEPTOTAL

NUMBER

Total amount of work to be done in the step

SOFAR

NUMBER

Total units of work done so far

TOTALWORK

NUMBER

Total amount of work to be done. Zero if unknown

APPLICATION
_DATA_1

NUMBER

This column may contain additional data relating to the long-running operation described by this row. The contents of this row vary depending on the type of long-running operation.

APPLICATION
_DATA_2

NUMBER

This column may contain additional data relating to the long-running operation described by this row. The contents of this row vary depending on the type of long-running operation.

APPLICATION
_DATA_3

NUMBER

This column may contain additional data relating to the long-running operation described by this row. The contents of this row vary depending on the type of long-running operation.

START_TIME

DATE

The starting time

CURRENT_TIME

DATE

The current time

ELAPSED
_SECONDS

NUMBER

The number of elapsed seconds

V$SESSION_OBJECT_CACHE

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

Column

Datatype
Description

PINS

NUMBER

Number of object pins or look-ups in the cache

HITS

NUMBER

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

TRUE_HITS

NUMBER

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

HIT_RATIO

NUMBER

The ratio of HITS/PINS

TRUE_HIT_RATIO

NUMBER

The ratio of TRUE_HITS/PINS

OBJECT_REFRESHES

NUMBER

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

CACHE_REFRESHES

NUMBER

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

OBJECT_FLUSHES

NUMBER

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

CACHE_FLUSHES

NUMBER

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

CACHE_SHRINKS

NUMBER

Number of times the cache was shrunk to the optimal size

CACHED_OBJECTS

NUMBER

Number of objects currently cached

PINNED_OBJECTS

NUMBER

Number of objects currently pinned

CACHE_SIZE

NUMBER

Current size of the cache in bytes

OPTIMAL_SIZE

NUMBER

Optimal size of the cache in bytes

MAXIMUM_SIZE

NUMBER

Maximum size of the cache in bytes

V$SESSION_WAIT

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

The following are tuning considerations:

P1RAW, P2RAW, and P3RAW display the same values as the P1, P2, and P3 columns, except that the numbers are displayed in hexadecimal.
The WAIT_TIME column contains a value of -2 on platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, you must set the TIMED_STATISTICS parameter to TRUE. Remember that doing this has a small negative effect on system performance. For more information, see "TIMED_STATISTICS" on page 1-122.
In previous releases the WAIT_TIME column contained an arbitrarily large value instead of a negative value to indicate the platform did not have a fast timing mechanism.
The STATE column interprets the value of WAIT_TIME and describes the state of the current or most recent wait.

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

Column Datatype Description

SID

NUMBER

Session identifier

SEQ#

NUMBER

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

EVENT

VARCHAR2(64)

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

P1TEXT

VARCHAR2

Description of first additional parameter

P1

NUMBER

First additional parameter

P1RAW

RAW(4)

First additional parameter

P2TEXT

VARCHAR2

Description of second parameter

P2

NUMBER

Second additional parameter

P2RAW

RAW(4)

Second additional parameter

P3TEXT

VARCHAR2

Description of third parameter

P3

NUMBER

Third additional parameter

P3RAW

RAW(4)

Third additional parameter

WAIT_TIME

NUMBER

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

STATE

VARCHAR2

Wait state (see Table 3-12)

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

Table 3-12 Wait State listed in the STATE Column

STATE	Value	Meaning
WAITING	0	The session is currently waiting
WAITED UNKNOWN TIME	-2	Duration of last wait is unknown
WAITED SHORT TIME	-1	Last wait < 1/100th of a second
WAITED KNOWN TIME	>0	WAIT_TIME = duration of last wait

V$SESSTAT

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

Column Datatype Description

SID

NUMBER

Session identifier

STATISTIC#

NUMBER

Statistic number (identifier)

VALUE

NUMBER

Statistic value

V$SESS_IO

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

Column Datatype Description

SID

NUMBER

Session identifier

BLOCK_GETS

NUMBER

Block gets for this session

CONSISTENT_GETS

NUMBER

Consistent gets for this session

PHYSICAL_READS

NUMBER

Physical reads for this session

BLOCK_CHANGES

NUMBER

Block changes for this session

CONSISTENT
_CHANGES

NUMBER

Consistent changes for this session

V$SGA

This view contains summary information on the System Global Area.

Column Datatype Description

NAME

VARCHAR2

SGA component group

VALUE

NUMBER

Memory size in bytes

V$SGASTAT

This view contains detailed information on the System Global Area.

Column Datatype Description

NAME

VARCHAR2

SGA component name

BYTES

NUMBER

Memory size in bytes

POOL

VARCHAR2

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

LARGE POOL - Memory is allocated from the large pool

or

SHARED POOL - Memory is allocated from the shared pool

V$SHARED_POOL_RESERVED

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

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

Column Datatype Description

FREE_SPACE

NUMBER

Total amount of free space on the reserved list

AVG_FREE_SIZE

NUMBER

Average size of the free memory on the reserved list

FREE_COUNT

NUMBER

Number of free pieces of memory on the reserved list

MAX_FREE_SIZE

NUMBER

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

USED_SPACE

NUMBER

Total amount of used memory on the reserved list

AVG_USED_SIZE

NUMBER

Average size of the used memory on the reserved list

USED_COUNT

NUMBER

Number of used pieces of memory on the reserved list

MAX_USED_SIZE

NUMBER

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

REQUESTS

NUMBER

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

REQUEST_MISSES

NUMBER

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

LAST_MISS_SIZE

NUMBER

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

MAX_MISS_SIZE

NUMBER

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

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

Column Datatype Description

REQUEST_FAILURES

NUMBER

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

LAST_FAILURE_SIZE

NUMBER

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

ABORTED_REQUEST _THRESHOLD

NUMBER

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

ABORTED_REQUESTS

NUMBER

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

LAST_ABORTED_SIZE

NUMBER

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

V$SHARED_SERVER

This view contains information on the shared server processes.

Column Datatype Description

NAME

VARCHAR2

Name of the server

PADDR

RAW(4)

Server's process address

STATUS

VARCHAR2

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

MESSAGES

NUMBER

Number of messages processed

BYTES

NUMBER

Total number of bytes in all messages

BREAKS

NUMBER

Number of breaks

CIRCUIT

RAW(4)

Address of circuit currently being serviced

IDLE

NUMBER

Total idle time in hundredths of a second

BUSY

NUMBER

Total busy time in hundredths of a second

REQUESTS

NUMBER

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

V$SORT_SEGMENT

This view contains information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.

Column Datatype Description

TABLESPACE_NAME

VARCHAR2(31)

Name of tablespace

SEGMENT_FILE

NUMBER

File number of the first extent

SEGMENT_BLOCK

NUMBER

Block number of the first extent

EXTENT_SIZE

NUMBER

Extent size

CURRENT_USERS

NUMBER

Number of active users of the segment

TOTAL_EXTENTS

NUMBER

Total number of extents in the segment

TOTAL_BLOCKS

NUMBER

Total number of blocks in the segment

RELATIVE_FNO

NUMBER

Relative file number of the sort segment header

USED_EXTENTS

NUMBER

Extents allocated to active sorts

USED_BLOCKS

NUMBER

Blocks allocated to active sorts

FREE_EXTENTS

NUMBER

Extents not allocated to any sort

FREE_BLOCKS

NUMBER

Blocks not allocated to any sort

ADDED_EXTENTS

NUMBER

Number of extent allocations

EXTENT_HITS

NUMBER

Number of times an unused extent was found in the pool

FREED_EXTENTS

NUMBER

Number of deallocated extents

FREE_REQUESTS

NUMBER

Number of requests to deallocate

MAX_SIZE

NUMBER

Maximum number of extents ever used

MAX_BLOCKS

NUMBER

Maximum number of blocks ever used

MAX_USED_SIZE

NUMBER

Maximum number of extents used by all sorts

MAX_USED_BLOCKS

NUMBER

Maximum number of blocks used by all sorts

MAX_SORT_SIZE

NUMBER

Maximum number of extents used by an individual sort

MAX_SORT_BLOCKS

NUMBER

Maximum number of blocks used by an individual sort

V$SORT_USAGE

This view describes sort usage.

Column Datatype Description

USER

VARCHAR2(30)

User who requested temporary space

SESSION_ADDR

RAW(4)

Address of shared SQL cursor

SESSION_NUM

NUMBER

Serial number of session

SQLADDR

RAW(4)

Address of SQL statement

SQLHASH

NUMBER

Hash value of SQL statement

TABLESPACE

VARCHAR2(31)

Tablespace in which space is allocated

CONTENTS

VARCHAR2(9)

Indicates whether tablespace is TEMPORARY/PERMANENT

SEGFILE#

NUMBER

File number of initial extent

SEGBLK#

NUMBER

Block number of the initial extent

EXTENTS

NUMBER

Extents allocated to the sort

BLOCKS

NUMBER

Extents in blocks allocated to the sort

SEGRFNO#

NUMBER

Relative file number of initial extent

V$SQL

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

V$SQL_BIND_DATA

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

Column Datatype Description

CURSOR_NUM

NUMBER

Cursor number for this bind

POSITION

NUMBER

Bind position

DATATYPE

NUMBER

Bind datatype

SHARED_MAX_LEN

NUMBER

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

PRIVATE_MAX_LEN

NUMBER

Private maximum length for this bind sent from the client

ARRAY_SIZE

NUMBER

Maximum number of array elements (for array binds only)

PRECISION

NUMBER

Precision (for numeric binds)

SCALE

NUMBER

Scale (for numeric binds)

SHARED_FLAG

NUMBER

Shared bind data flags

SHARED_FLAG2

NUMBER

Shared bind data flags (continued)

BUF_ADDRESS

RAW(4)

Bind buffer memory address

BUF_LENGTH

NUMBER

Bind buffer length

VAL_LENGTH

NUMBER

Actual bind value length

BUF_FLAG

NUMBER

Bind buffer flags

INDICATOR

NUMBER

Bind indicator

VALUE

VARCHAR2(4000)

Contents of the bind buffer

V$SQL_BIND_METADATA

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

Column Datatype Description

ADDRESS

RAW(4)

Memory address of the child cursor that owns this bind variable

POSITION

NUMBER

Bind position

DATATYPE

NUMBER

Bind datatype

MAX_LENGTH

NUMBER

Maximum length of the bind value

ARRAY_LEN

NUMBER

Maximum number of array elements (for array binds only)

BIND_NAME

VARCHAR2(30)

Bind variable name (if used)

V$SQL_CURSOR

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

Column Datatype Description

CURNO

NUMBER

Cursor number

FLAG

NUMBER

Flags set in the cursor

STATUS

VARCHAR2(9)

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

PARENT_HANDLE

RAW(4)

Pointer to the parent cursor handle

PARENT_LOCK

RAW(4)

Pointer to the parent cursor lock

CHILD_LOCK

RAW(4)

Pointer to the child cursor lock

CHILD_PIN

RAW(4)

Pointer to the child cursor pin

PERS_HEAP_MEM

NUMBER

Total amount of memory allocated from persistent heap for this cursor

WORK_HEAP_MEM

NUMBER

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

BIND_VARS

NUMBER

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

DEFINE_VARS

NUMBER

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

BIND_MEM_LOC

VARCHAR2(64)

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

INST_FLAG

VARCHAR2(64)

Instantiation object flags

INST_FLAG2

VARCHAR2(64)

Instantiation object flags (continued)

V$SQL_SHARED_MEMORY

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

Column Datatype Description

SQL_TEXT

VARCHAR2(1000)

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

HASH_VALUE

NUMBER

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

HEAP_DESC

RAW(4)

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

STRUCTURE

VARCHAR2(16)

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

FUNCTION

VARCHAR2(16)

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

COMMENT

VARCHAR2(16)

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

CHUNK_PTR

RAW(4)

This is the starting address of the allocated memory chunk.

CHUNK_SIZE

NUMBER

The amount of memory allocated for this chunk.

ALLOC_CLASS

VARCHAR2(8)

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

CHUNK_TYPE

NUMBER

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

SUBHEAP_DESC

RAW(4)

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

V$SQLAREA

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

Column Datatype Description

SQL_TEXT

VARCHAR2(1000)

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

SHARABLE_MEM

NUMBER

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

PERSISTENT_MEM

NUMBER

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

RUNTIME_MEM

NUMBER

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

SORTS

NUMBER

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

VERSION_COUNT

NUMBER

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

LOADED_VERSIONS

NUMBER

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

OPEN_VERSIONS

NUMBER

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

USERS_OPENING

NUMBER

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

EXECUTIONS

NUMBER

The total number of executions, totalled over all the children

USERS_EXECUTING

NUMBER

The total number of users executing the statement over all children

LOADS

NUMBER

The number of times the object was loaded or reloaded

FIRST_LOAD_TIME

VARCHAR2(19)

The time stamp of the parent creation time

INVALIDATIONS

NUMBER

The total number of invalidations over all the children

PARSE_CALLS

NUMBER

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

DISK_READS

NUMBER

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

BUFFER_GETS

NUMBER

The sum of buffer gets over all child cursors

ROWS_PROCESSED

NUMBER

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

COMMAND_TYPE

NUMBER

The Oracle command type definition

OPTIMIZER_MODE

VARCHAR2(10)

Mode under which the SQL statement is executed

PARSING_USER_ID

NUMBER

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

PARSING_SCHEMA_ID

NUMBER

The schema ID that was used to parse this child cursor

KEPT_VERSIONS

NUMBER

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

ADDRESS

RAW(4)

The address of the handle to the parent for this cursor

HASH_VALUE

NUMBER

The hash value of the parent statement in the library cache

MODULE

VARCHAR2(64)

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

MODULE_HASH

NUMBER

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

ACTION

VARCHAR2(64)

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

ACTION_HASH

NUMBER

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

SERIALIZABLE_ABORTS

NUMBER

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

V$SQLTEXT

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

Column Datatype Description

ADDRESS

RAW

Used with HASH_VALUE to identify uniquely a cached cursor

HASH_VALUE

NUMBER

Used with ADDRESS to identify uniquely a cached cursor

PIECE

NUMBER

Number used to order the pieces of SQL text

SQL_TEXT

VARCHAR2

A column containing one piece of the SQL text

COMMAND_TYPE

NUMBER

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

V$SQLTEXT_WITH_NEWLINES

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

V$STATNAME

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

Column Datatype Description

STATISTIC#

NUMBER

Statistic number

NAME

VARCHAR2

Statistic name. See also Table 3-13

CLASS

NUMBER

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

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

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

CPU used by this session	CR blocks created
Current blocks converted for CR	DBWR Flush object call found no dirty buffers
DBWR Flush object cross instance calls	DBWR buffers scanned
DBWR checkpoints	DBWR cross instance writes
DBWR free buffers found	DBWR lru scans
DBWR make free requests	DBWR summed scan depth
DBWR timeouts	DDL statements parallelized
DML statements parallelized	PX local messages received
PX local messages sent	PX remote messages received
PX remote messages sent	SQL*Net roundtrips to/from client
SQL*Net roundtrips to/from dblink	Unnecessary process cleanup for SCN batching
background checkpoints completed	background checkpoints started
bytes received via SQL*Net from client	bytes received via SQL*Net from client
bytes sent via SQL*Net to client	bytes sent via SQL*Net to dblink
calls to get snapshot scn: kcmgss	change write time
cluster key scan block gets	cluster key scans
commit cleanout failures: block lost	commit cleanout failures: buffer being written
commit cleanout failures: callback failure	commit cleanout failures: cannot pin
commit cleanout failures: hot backup in progress	commit cleanout failures: write disabled
commit cleanouts	commit cleanouts successfully completed
consistent changes	consistent gets
cross instance CR read	db block changes
db block gets	dirty buffers inspected
enqueue conversions	enqueue deadlocks
enqueue releases	enqueue requests
enqueue timeouts	enqueue waits
exchange deadlocks	execute count
free buffer inspected	free buffer requested
global cache defers	global cache freelist waits
global cache hash latch waits	global lock convert time
global lock converts (async)	global lock converts (non async)
global lock get time	global lock gets (async)
global lock gets (non async)	global lock release time
global lock releases	kcmccs called get current scn
kcmccs read scn without going to DLM	kcmccs waited for batching
lock element waits	logons cumulative
logons current	next scns gotten without going to DLM
opened cursors cumulative	opened cursors current
opens of replaced files	pens requiring cache replacement
parse count (hard)	parse count (soft)
parse count (total)	parse time cpu
parse time elapsed	physical reads
physical writes	queries parallelized
recovery array read time	recovery array reads
recovery blocks read	recursive calls
recursive cpu usage	redo entries
redo entries linearized	redo log space requests
redo log space wait time	redo log switch interrupts
redo ordering marks	redo size
redo synch time	redo sync writes
redo wastage	redo write time
redo writer latching time	redo writes
remote instance undo block writes	remote instance undo header writes
remote instance undo requests	serializable aborts
session connect time	session cursor cache count
session cursor cache hits	session logical reads
session pga memory	session pga memory max
session stored procedure space	session uga memory
session uga memory max	sorts (disk)
sorts (memory)	sorts (rows)
summed dirty queue length	table fetch by rowid
table fetch continued row	table scan blocks gotten
table scan rows gotten	table scans (cache partitions)
table scans (direct read)	table scans (long tables)
table scans (rowid ranges)	table scans (short tables)
total file opens	user calls
user commits	user rollbacks
write requests

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

V$SUBCACHE

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

Column Datatype Description

OWNER_NAME

VARCHAR2(64)

Owner of object containing these cache entries

NAME

VARCHAR2(1000)

Object Name

TYPE

NUMBER

Object Type

HEAP_NUM

NUMBER

Heap number containing this subordinate cache

CACHE_ID

NUMBER

Subordinate cache ID

CACHE_CNT

NUMBER

Number of entries for this cache in this object

HEAP_SZ

NUMBER

Amount of extent space allocated to this heap

HEAP_ALOC

NUMBER

Amount of extent space allocated from this heap

HEAP_USED

NUMBER

Amount of space utilized in this heap

V$SYSSTAT

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

Column Datatype Description

STATISTIC#

NUMBER

Statistic number

NAME

VARCHAR2(64)

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

CLASS

NUMBER

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

VALUE

NUMBER

Statistic value

V$SYSTEM_CURSOR_CACHE

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

Column Datatype Description

OPENS

NUMBER

Cumulative total of cursor opens

HITS

NUMBER

Cumulative total of cursor open hits

HIT_RATIO

NUMBER

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

V$SYSTEM_EVENT

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

Column Datatype Description

EVENT

VARCHAR2(64)

The name of the wait event

TOTAL_WAITS

NUMBER

The total number of waits for this event

TOTAL_TIMEOUTS

NUMBER

The total number of timeouts for this event

TIME_WAITED

NUMBER

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

AVERAGE_WAIT

NUMBER

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

V$SYSTEM_PARAMETER

This view contains information on system parameters.

Column Datatype Description

NUM

NUMBER

Parameter number

NAME

VARCHAR2(64)

Parameter name

TYPE

NUMBER

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

VALUE

VARCHAR2(512)

Value assigned to the parameter

ISDEFAULT

VARCHAR2(9)

Is the value assigned to the parameter the default

ISSES_MODIFIABLE

VARCHAR2(5)

Whether the parameter can be modified by ALTER SESSION

ISSYS_MODIFIABLE

VARCHAR2(9)

Whether the parameter can be modified by ALTER SYSTEM

ISMODIFIED

VARCHAR2(8)

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

ISADJUSTED

VARCHAR2(5)

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

DESCRIPTION

VARCHAR2(64)

Descriptive text about the parameter

V$TABLESPACE

This view displays tablespace information from the controlfile.

Column Datatype Description

TS#

NUMBER

Tablespace number

NAME

VARCHAR2 (30)

Tablespace name

V$THREAD

This view contains thread information from the control file.

Column Datatype Description

THREAD#

NUMBER

Thread number

STATUS

VARCHAR2

Thread status: OPEN, CLOSED

ENABLED

VARCHAR2

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

ENABLE_CHANGE#

NUMBER

SCN at which thread was enabled

ENABLE_TIME

DATE

Time of enable SCN

DISABLE_CHANGE#

NUMBER

SCN at which thread was disabled

DISABLE_TIME

DATE

Time of disable SCN

GROUPS

NUMBER

Number of log groups assigned to this thread

INSTANCE

VARCHAR2

Instance name, if available

OPEN_TIME

DATE

Last time the thread was opened

CURRENT_GROUP#

NUMBER

Current log group

SEQUENCE#

NUMBER

Sequence number of current log

CHECKPOINT
_CHANGE#

NUMBER

SCN at last checkpoint

CHECKPOINT_TIME

DATE

Time of last checkpoint

V$TIMER

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

Column Datatype Description

HSECS

NUMBER

Elapsed time in hundredths of a second

V$TRANSACTION

This view lists the active transactions in the system.

Column Datatype Description

ADDR

RAW(4)

Address of transaction state object

XIDUSN

NUMBER

Undo segment number

XIDSLOT

NUMBER

Slot number

XIDSQN

NUMBER

Sequence number

UBAFIL

NUMBER

Undo block address (UBA) filenum

UBABLK

NUMBER

UBA block number

UBASQN

NUMBER

UBA sequence number

UBAREC

NUMBER

UBA record number

STATUS

VARCHAR2(16)

Status

START_TIME

VARCHAR2(20)

Start time (wall clock)

START_SCNB

NUMBER

Start system change number (SCN) base

START_SCNW

NUMBER

Start SCN wrap

START_UEXT

NUMBER

Start extent number

START_UBAFIL

NUMBER

Start UBA file number

START_UBABLK

NUMBER

Start UBA block number

START_UBASQN

NUMBER

Start UBA sequence number

START_UBAREC

NUMBER

Start UBA record number

SES_ADDR

RAW(4)

User session object address

FLAG

NUMBER

Flag

SPACE

VARCHAR2(3)

"Yes", if a space transaction

RECURSIVE

VARCHAR2(3)

"Yes", if a recursive transaction

NOUNDO

VARCHAR2(3)

"Yes" if a no undo transaction

PTX

VARCHAR 2(3)

YES if parallel transaction, otherwise set to NO

PRV_XIDUSN

NUMBER

Previous transaction undo segment number

PRV_XIDSLT

NUMBER

Previous transaction slot number

PRV_XIDSQN

NUMBER

Previous transaction sequence number

PTX_XIDUSN

NUMBER

Rollback segment number of the parent XID

PTX_XIDSLT

NUMBER

Slot number of the parent XID

PTX_XIDSQN

NUMBER

Sequence number of the parent XID

DSCN_B

NUMBER

Dependent SCN base

DSCN_W

NUMBER

Dependent SCN wrap

USED_UBLK

NUMBER

Number of undo blocks used

USED_UREC

NUMBER

Number of undo records used

LOG_IO

NUMBER

Logical I/O

PHY_IO

NUMBER

Physical I/O

CR_GET

NUMBER

Consistent gets

CR_CHANGE

NUMBER

Consistent changes

V$TRANSACTION_ENQUEUE

V$TRANSACTION_ENQUEUE displays locks owned by transaction state objects.

Column Datatype Description

ADDR

RAW(4)

Address of lock state object

KADDR

RAW(4)

Address of lock

SID

NUMBER

Identifier for session holding or acquiring the lock

TYPE

VARCHAR2(2)

Type of lock. TX = transaction enqueue.

ID1

NUMBER

Lock identifier #1 (depends on type)

ID2

NUMBER

Lock identifier #2 (depends on type)

LMODE

NUMBER

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

REQUEST

NUMBER

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

CTIME

NUMBER

Time since current mode was granted

BLOCK

NUMBER

The lock is blocking another lock

V$TYPE_SIZE

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

Column Datatype Description

COMPONENT

VARCHAR2

Component name, such as segment or buffer header

TYPE

VARCHAR2

Component type

DESCRIPTION

VARCHAR2

Description of component

TYPE_SIZE

NUMBER

Size of component

V$VERSION

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

Column Datatype Description

BANNER

VARCHAR2

Component name and version number

V$WAITSTAT

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

Column Datatype Description

CLASS

VARCHAR2

Class of block

COUNT

NUMBER

Number of waits by this OPERATION for this CLASS of block

TIME

NUMBER

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

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.

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

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

Column	Datatype	Description
NAME	VARCHAR2	Name of the database
CREATED	DATE	Creation date
LOG_MODE	VARCHAR2	Archive log mode: NOARCHIVELOG or ARCHIVELOG
CHECKPOINT_ CHANGE#	NUMBER	Last SCN checkpointed
ARCHIVE_CHANGE#	NUMBER	Last SCN archived
DBID	NUMBER	Database ID calculated when database is created and stored in all file headers
RESETLOGS_CHANGE#	NUMBER	Change# at open resetlogs
RESETLOGS_TIME	DATE	Timestamp of open resetlogs
CONTROLFILE_TYPE	VARCHAR2(7)	CURRENT/STANDBY/CLONE/BACKUP/CREATED. STANDBY indicates database is in standby mode. CLONE indicates a clone database. BACKUP/CREATED indicates database is being recovered using a backup or created controlfile. A standby database activate or database open after recovery changes the type to CURRENT
CONTROLFILE_CREATED	DATE	Controlfile creation timestamp
CONTROLFILE_SEQUENCE#	NUMBER	Controlfile sequence number incremented by controlfile transactions
CONTROLFILE_CHANGE#	NUMBER	Last change# in backup controlfile. Set to NULL if the controlfile is not a backup
CONTROLFILE_TIME	DATE	Last timestamp in backup controlfile. Set to NULL if the controlfile is not a backup
OPEN_RESETLOGS	VARCHAR2(11)	NOT ALLOWED/ALLOWED/REQUIRED. Indicates whether next database open allows or requires the resetlogs option

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

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

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

Column	Datatype	Description
OWNERID	NUMBER	The owner ID of the owner if this is a private pipe; NULL otherwise.
NAME	VARCHAR2	The name of the pipe; for example, scott.pipe
TYPE	VARCHAR2	PUBLIC or PRIVATE
PIPE_SIZE	NUMBER	The amount of memory the pipe uses

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

Column	Datatype	Description
NAME	VARCHAR2	Name of the dispatcher process
NETWORK	VARCHAR2	Network protocol supported by this dispatcher. For example, TCP or DECNET.
PADDR	RAW(4)	Process address
STATUS	VARCHAR2	Dispatcher status: WAIT (idle), SEND (sending a message connection), RECEIVE (receiving a message), CONNECT (establishing a connection), DISCONNECT (handling a disconnect request), BREAK (handling a break), OUTBOUND (establishing an outbound connection)
ACCEPT	VARCHAR2	Whether this dispatcher is accepting new connections: YES, NO
MESSAGES	NUMBER	Number of messages processed by this dispatcher
BYTES	NUMBER	Size in bytes of messages processed by this dispatcher
BREAKS	NUMBER	Number of breaks occurring in this connection
OWNED	NUMBER	Number of circuits owned by this dispatcher
CREATED	NUMBER	Number of circuits created by this dispatcher
IDLE	NUMBER	Total idle time for this dispatcher in hundredths of a second
BUSY	NUMBER	Total busy time for this dispatcher in hundredths of a second
LISTENER	NUMBER	The most recent Oracle error number the dispatcher received from the listener

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

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.

Column	Datatype	Description
LATCH_TYPE	VARCHAR2(64)	The name of the latch type. See Table 3-4.
IMM_GETS	NUMBER	Immediate gets. The number of times that an attempt to acquire a latch of the specified type was satisfied immediately (that is, the process did not have to wait for another process to release the latch).
TTL_GETS	NUMBER	Total gets. The total number of times the latch was acquired.

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

Column	Datatype	Description
STATISTIC#	NUMBER	Statistic number
NAME	VARCHAR2(64)	Name of the statistic
VALUE	NUMBER	Value associated with the statistic

Column	Datatype	Description
ADDR	RAW(4)	Address of lock state object
KADDR	RAW(4)	Address of lock
SID	NUMBER	Identifier for session holding or acquiring the lock
TYPE	VARCHAR2(2)	Type of lock. For a list of user and system types that can have locks, see Table 3-4, "Values for the LATCH_TYPE column".
ID1	NUMBER	Lock identifier #1 (depends on type)
ID2	NUMBER	Lock identifier #2 (depends on type)
LMODE	NUMBER	Lock mode in which the session holds the lock: 0, None 1, Null (NULL) 2, Row-S (SS) 3, Row-X (SX) 4, Share (S) 5, S/Row-X (SSX) 6, Exclusive (X)
REQUEST	NUMBER	Lock mode in which the process requests the lock: 0, None 1, Null (NULL) 2, Row-S (SS) 3, Row-X (SX) 4, Share (S) 5, S/Row-X (SSX) 6, Exclusive (X)
CTIME	NUMBER	Time since current mode was granted
BLOCK	NUMBER	The lock is blocking another lock

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

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

Column	Datatype	Description
SID	NUMBER	Session ID
SERIAL#	NUMBER	Session serial number
OBJECT_NODE	VARCHAR2(20)	Name of the OBJECT_NODE in plan table
ELAPSED_TIME	NUMBER	Elapsed time for OBJECT_NODE

Column	Datatype	Description
FILE_NUMBER	NUMBER	Number of the datafile
FREQUENCY	NUMBER	The frequency
X_2_NULL	NUMBER	Number of lock conversions from Exclusive-to-NULL for all blocks in the file
X_2_NULL_FORCED _WRITE	NUMBER	Number of forced writes that occur for blocks of the specified file due to Exclusive-to-NULL conversions
X_2_NULL_FORCED _STALE	NUMBER	Number of times a block in the file was made STALE due to Exclusive-to-NULL conversions
X_2_S	NUMBER	Number of lock conversions from Exclusive-to-Shared for all blocks in the file
X_2_S_FORCED_WRITE	NUMBER	Number of forced writes that occur for blocks of the specified file due to Exclusive-to-Shared conversions
X_2_SSX	NUMBER	Number of lock conversions from Exclusive-to-Sub Shared Exclusive for all blocks in the file
X_2_SSX_FORCED _WRITE	NUMBER	Number of forced writes that occur for blocks of the specified file due to Exclusive-to-Sub Shared Exclusive conversions
S_2_NULL	NUMBER	Number of lock conversions from Shared-to-NULL for all blocks in the file
S_2_NULL_FORCED _STALE	NUMBER	Number of times a block in the file was made STALE due to Shared-to-NULL conversions
SS_2_NULL	NUMBER	Number of lock conversions from Sub Shared-to-NULL for all blocks in the file
WRB	NUMBER	Number of times the instance received a write single buffer cross instance call for this file
WRB_FORCED_WRITE	NUMBER	Number of blocks written due to write single buffer cross instance calls for this file
RBR	NUMBER	Number of times the instance received a resuse block range cross instance call for this file
RBR_FORCED_WRITE	NUMBER	Number of blocks written due to resuse block range cross instance calls for this file
RBR_FORCED_STALE	NUMBER	Number of times a block in this file was made STALE due to resuse block range cross instance calls
CBR	NUMBER	Number of times the instance received a checkpoint block range cross instance call for this file
CBR_FORCED_WRITE	NUMBER	Number of blocks in this file which were written due to checkpoint cross range cross instance calls
NULL_2_X	NUMBER	Number of lock conversions from NULL-to-Exclusive for all blocks of the specified file
S_2_X	NUMBER	Number of lock conversions from Shared-to-Exclusive for all blocks of the specified file
SSX_2_X	NUMBER	Number of lock conversions from Sub Shared Exclusive-to-Exclusive for all blocks of the specified file
NULL_2_S	NUMBER	Number of lock conversions from NULL-to-Shared for all blocks of the specified file
NULL_2_SS	NUMBER	Number of lock conversions from NULL-to-Sub Shared for all blocks of the specified file

3Dynamic Performance (V$) Views

Dynamic Performance Views

V$ Views

GV$ Views

Restrictions on GV$ Views

Access to the Dynamic Performance Tables

View Descriptions

FILEXT$

V$ACCESS

V$ACTIVE_INSTANCES

V$AQ

V$ARCHIVE

V$ARCHIVE_DEST

V$ARCHIVED_LOG

V$BACKUP

V$BACKUP_CORRUPTION

V$BACKUP_DATAFILE

V$BACKUP_DEVICE

V$BACKUP_PIECE

V$BACKUP_REDOLOG

V$BACKUP_SET

V$BGPROCESS

V$BH

V$BUFFER_POOL

V$CACHE

Table 3-1 Values for the KIND column

V$CACHE_LOCK

V$CIRCUIT

V$CLASS_PING

V$COMPATIBILITY

V$COMPATSEG

V$CONTROLFILE

V$CONTROLFILE_RECORD_SECTION

V$COPY_CORRUPTION

V$CURRENT_BUCKET

V$DATABASE

V$DATAFILE

Table 3-2 Values for the ENABLED Column

V$DATAFILE_COPY

V$DATAFILE_HEADER

V$DBFILE

V$DBLINK

V$DB_OBJECT_CACHE

V$DB_PIPES

V$DELETED_OBJECT

V$DISPATCHER

V$DISPATCHER_RATE

V$DLM_CONVERT_LOCAL

V$DLM_CONVERT_REMOTE

Table 3-3 Values for the CONVERT_TYPE column

V$DLM_LATCH

Table 3-4 Values for the LATCH_TYPE column

V$DLM_LOCKS

V$DLM_MISC

V$ENABLEDPRIVS

V$ENQUEUE_LOCK

V$EVENT_NAME

V$EXECUTION

V$EXECUTION_LOCATION

V$FALSE_PING

V$FILE_PING

V$FILESTAT

V$FIXED_TABLE

V$FIXED_VIEW_DEFINITION

V$GLOBAL_TRANSACTION

V$INDEXED_FIXED_COLUMN

V$INSTANCE

V$LATCH

V$LATCHHOLDER

V$LATCHNAME

V$LATCH_CHILDREN

V$LATCH_MISSES

V$LATCH_PARENT

V$LIBRARYCACHE

V$LICENSE

V$LOADCSTAT

V$LOADTSTAT

V$LOCK

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

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

3
Dynamic Performance (V$) Views

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

Column	Datatype	Description
VIEW_NAME	VARCHAR2(30)	The name of the fixed view
VIEW_DEFINITION	VARCHAR2(2000)	The definition of the fixed view

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

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)

Column	Datatype	Description
INSTANCE_NUMBER	NUMBER	Instance number used for instance registration. Corresponds to INSTANCE_NUMBER initialization parameter. See "INSTANCE_NUMBER" on page 1-51.
INSTANCE_NAME	VARCHAR2(16)	Instance name.
HOST_NAME	VARCHAR2(64)	Name of the host machine
VERSION	VARCHAR2(17)	RDBMS version
STARTUP_TIME	DATE	Time when instance was started up
STATUS	VARCHAR2(7)	STARTED/MOUNTED/OPEN STARTED after startup nomount MOUNTED after startup mount or alter database close OPEN after startup or after database open
PARALLEL	VARCHAR2(3)	YES/NO in parallel server mode
THREAD#	NUMBER	Redo thread opened by the instance
ARCHIVER	VARCHAR2(7)	STOPPED/STARTED/FAILED FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes
LOG_SWITCH_WAIT	VARCHAR2(11)	ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then value is NULL
LOGINS	VARCHAR2(10)	ALLOWED/RESTRICTED
SHUTDOWN_PENDING	VARCHAR2(3)	YES/NO

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

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

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

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

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