oracle 性能优化 08_字典视图-白红宇

oracle 性能优化 08_字典视图

阅读量：5974 次

发布时间：2019-06-19

本文共 120184 字，大约阅读时间需要 400 分钟。

性能查询相关视图

V$SYSMETRIC

V$SYSMETRIC displays the system metric values captured for the most current time interval for both the long duration (60-second) and short duration (15-second) system metrics.

Column	Datatype	Description
BEGIN_TIME	DATE	Begin time of the interval
END_TIME	DATE	End time of the interval
INTSIZE_CSEC	NUMBER	Interval size (in hundredths of a second)
GROUP_ID	NUMBER	Metric group ID
METRIC_ID	NUMBER	Metric ID
METRIC_NAME	VARCHAR2(64)	Metric name
VALUE	NUMBER	Metric value
METRIC_UNIT	VARCHAR2(64)	Metric unit description

V$SYSMETRIC_HISTORY

V$SYSMETRIC_HISTORY displays all system metric values available in the database. Both long duration (60-second with 1 hour history) and short duration (15-second with one-interval only) metrics are displayed by this view.

Column	Datatype	Description
BEGIN_TIME	DATE	Begin time of the interval
END_TIME	DATE	End time of the interval
INTSIZE_CSEC	NUMBER	Interval size (in hundredths of a second)
GROUP_ID	NUMBER	Metric group ID
METRIC_ID	NUMBER	Metric ID
METRIC_NAME	VARCHAR2(64)	Metric name
VALUE	NUMBER	Metric value
METRIC_UNIT	VARCHAR2(64)	Metric unit description

V$SYSMETRIC_SUMMARY

V$SYSMETRIC_SUMMARY displays a summary of all system Metric values for the long-duration system metrics. The average, maximum value, minimum value, and the value of one standard deviation for the last hour are displayed for each metric item.

Column	Datatype	Description
`BEGIN_TIME`	`DATE`	`Begin time of the interval`
`END_TIME`	`DATE`	`End time of the interval`
`INTSIZE_CSEC`	`NUMBER`	`Interval size (in hundredths of a second)`
`GROUP_ID`	`NUMBER`	`Metric group ID`
`METRIC_ID`	`NUMBER`	`Metric ID`
`METRIC_NAME`	`VARCHAR2(64)`	`Metric name`
`NUM_INTERVAL`	`NUMBER`	`Number of intervals observed`
`MAXVAL`	`NUMBER`	`Maximum value observed`
`MINVAL`	`NUMBER`	`Minimum value observed`
`AVERAGE`	`NUMBER`	`Average value over the period`
`STANDARD_DEVIATION`	`NUMBER`	`One standard deviation`
`METRIC_UNIT`	`VARCHAR2(64)`	`Metric unit description`

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; doing this will have a small negative effect on system performance.

Column	Datatype	Description
EVENT	VARCHAR2(64)	Name of the wait event
TOTAL_WAITS	NUMBER	Total number of waits for the event
TOTAL_TIMEOUTS	NUMBER	Total number of timeouts for the event
TIME_WAITED	NUMBER	Total amount of time waited for the event (in hundredths of a second)
AVERAGE_WAIT	NUMBER	Average amount of time waited for the event (in hundredths of a second)
TIME_WAITED_MICRO	NUMBER	Total amount of time waited for the event (in microseconds)
EVENT_ID	NUMBER	Identifier of the wait event
WAIT_CLASS_ID	NUMBER	Identifier of the class of the wait event
WAIT_CLASS#	NUMBER	Number of the class of the wait event
WAIT_CLASS	VARCHAR2(64)	Name of the class of the wait event

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.

Column	Datatype	Description
SID	NUMBER	ID of the session
EVENT	VARCHAR2(64)	Name of the wait event See Also: Appendix C, "Oracle Wait Events"
TOTAL_WAITS	NUMBER	Total number of waits for the event by the session
TOTAL_TIMEOUTS	NUMBER	Total number of timeouts for the event by the session
TIME_WAITED	NUMBER	Total amount of time waited for the event by the session (in hundredths of a second)
AVERAGE_WAIT	NUMBER	Average amount of time waited for the event by the session (in hundredths of a second)
MAX_WAIT	NUMBER	Maximum time waited for the event by the session (in hundredths of a second)
TIME_WAITED_MICRO	NUMBER	Total amount of time waited for the event by the session (in microseconds)
EVENT_ID	NUMBER	Identifier of the wait event
WAIT_CLASS_ID	NUMBER	Identifier of the class of the wait event
WAIT_CLASS#	NUMBER	Number of the class of the wait event
WAIT_CLASS	VARCHAR2(64)	Name of the class of the wait event

V$SESSION_WAIT

V$SESSION_WAIT displays 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, then you must set theTIMED_STATISTICS initialization parameter to true. Remember that doing this has a small negative effect on system performance.

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.

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` `See Also:` `Appendix C, "Oracle Wait Events"`
`P1TEXT`	`VARCHAR2(64)`	`Description of the first additional parameter`
`P1`	`NUMBER`	`First additional parameter`
`P1RAW`	`RAW(4)`	`First additional parameter`
`P2TEXT`	`VARCHAR2(64)`	`Description of the second additional parameter`
`P2`	`NUMBER`	`Second additional parameter`
`P2RAW`	`RAW(4)`	`Second additional parameter`
`P3TEXT`	`VARCHAR2(64)`	`Description of the third additional parameter`
`P3`	`NUMBER`	`Third additional parameter`
`P3RAW`	`RAW(4)`	`Third additional parameter`
`WAIT_CLASS_ID`	`NUMBER`	`Identifier of the wait class`
`WAIT_CLASS#`	`NUMBER`	`Number of the wait class`
`WAIT_CLASS`	`VARCHAR2(64)`	`Name of the wait class`
`WAIT_TIME`	`NUMBER`	`A nonzero value is the session's last wait time. A zero value means the session is currently waiting.`
`SECONDS_IN_WAIT`	`NUMBER`	`If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is the active seconds since the last wait ended.`
`STATE`	`VARCHAR2(19)`	`Wait state:` `0 - WAITING (the session is currently waiting)` `-2 - WAITED UNKNOWN TIME (duration of last wait is unknown)` `-1 - WAITED SHORT TIME (last wait <1/100th of a second)` `>0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)`

V$EVENT_HISTOGRAM

V$EVENT_HISTOGRAM displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.

The histogram will not be filled unless the TIMED_STATISTICS initialization parameter is set to true.

Column	Datatype	Description
`EVENT#`	`NUMBER`	`Event number`
`EVENT`	`VARCHAR2(64)`	`Name of the Event`
`WAIT_TIME_MILLI`	`NUMBER`	`Amount of time the bucket represents (in milliseconds). If the duration = num, then this column represents waits of duration < num that are not included in any smaller bucket.`
`WAIT_COUNT`	`NUMBER`	`Number of waits of the duration belonging to the bucket of the histogram`

V$FILE_HISTOGRAM、V$TEMP_HISTOGRAM

V$FILE_HISTOGRAM displays a histogram of all single block reads on a per-file basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.

The histogram will not be filled unless the TIMED_STATISTICS initialization parameter is set to true.

Column	Datatype	Description
`FILE#`	`NUMBER`	`File number`
`SINGLEBLKRDTIM_MILLI`	`NUMBER`	`Amount of time the bucket represents (in milliseconds). If the duration = num, then this column represents waits of duration < num that are not included in any smaller bucket.`
`SINGLEBLKRDS`	`NUMBER`	`Number of waits of the duration belonging to the bucket of the histogram`

V$SYSTEM_WAIT_CLASS

V$SYSTEM_WAIT_CLASS displays the instance-wide time totals for each registered wait class.

Column	Datatype	Description
`WAIT_CLASS_ID`	`NUMBER`	`Identifier of the wait class`
`WAIT_CLASS#`	`NUMBER`	`Number of the wait class`
`WAIT_CLASS`	`VARCHAR2(64)`	`Name of the wait class`
`TOTAL_WAITS`	`NUMBER`	`Number of times waits of the class occurred`
`TIME_WAITED`	`NUMBER`	`Amount of time spent in the wait by all sessions in the instance`

V$SESSION_WAIT_CLASS

V$SESSION_WAIT_CLASS displays the time spent in various wait event operations on a per-session basis.

Column	Datatype	Description
`SID`	`NUMBER`	`Session identifier`
`SERIAL#`	`NUMBER`	`Serial number`
`WAIT_CLASS_ID`	`NUMBER`	`Identifier of the wait class`
`WAIT_CLASS#`	`NUMBER`	`Number of the wait class`
`WAIT_CLASS`	`VARCHAR2(64)`	`Name of the wait class`
`TOTAL_WAITS`	`NUMBER`	`Number of times waits of the class occurred for the session`
`TIME_WAITED`	`NUMBER`	`Amount of time spent in the wait class by the session`

V$SESSION

This view lists session information for each current session.

Column	Datatype	Description
SADDR	RAW(4 \| 8)	Session address
SID	NUMBER	Session identifier
SERIAL#	NUMBER	Session serial number. Used to uniquely identify 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 \| 8)	Address of the process that owns the 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 7-5. These values also appear in the AUDIT_ACTIONStable.
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 4-byte value. The low-order 2 bytes 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 - Session currently executing SQL INACTIVE KILLED - Session marked to be killed CACHED - Session 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(30)	Operating system client user name
PROCESS	VARCHAR2(12)	Operating system client process ID
MACHINE	VARCHAR2(64)	Operating system machine name
TERMINAL	VARCHAR2(30)	Operating system terminal name
PROGRAM	VARCHAR2(48)	Operating system program name
TYPE	VARCHAR2(10)	Session type
SQL_ADDRESS	RAW(4 \| 8)	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
SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement that is currently being executed
SQL_CHILD_NUMBER	NUMBER	Child number of the SQL statement that is currently being executed
PREV_SQL_ADDR	RAW(4 \| 8)	Used with PREV_HASH_VALUE to identify the last SQL statement executed
PREV_HASH_VALUE	NUMBER	Used with SQL_HASH_VALUE to identify the last SQL statement executed
PREV_SQL_ID	VARCHAR2(13)	SQL identifier of the last SQL statement executed
PREV_CHILD_NUMBER	NUMBER	Child number of the last SQL statement executed
MODULE	VARCHAR2(48)	Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
MODULE_HASH	NUMBER	Hash value of the above MODULE
ACTION	VARCHAR2(32)	Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure
ACTION_HASH	NUMBER	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 row specified in ROW_WAIT_ROW#
ROW_WAIT_FILE#	NUMBER	Identifier for the datafile containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.
ROW_WAIT_BLOCK#	NUMBER	Identifier for the block containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.
ROW_WAIT_ROW#	NUMBER	Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.
LOGON_TIME	DATE	Time of logon
LAST_CALL_ET	NUMBER	If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active. If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.
PDML_ENABLED	VARCHAR2(3)	This column has been replaced by column PDML_STATUS
FAILOVER_TYPE	VARCHAR2(13)	Indicates whether and to what extent transparent application failover (TAF) is enabled for the session: NONE - Failover is disabled for this session SESSION - Client is able to fail over its session following a disconnect SELECT - Client is able to fail over queries in progress as well See Also: Oracle Database Concepts for more information on TAF Oracle Database Net Services Administrator's Guide for information on configuring TAF
FAILOVER_METHOD	VARCHAR2(10)	Indicates the transparent application failover method for the session: NONE - Failover is disabled for this session BASIC - Client itself reconnects following a disconnect PRECONNECT - Backup instance can support all connections from every instance for which it is backed up
FAILED_OVER	VARCHAR2(3)	Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO)
RESOURCE_CONSUMER_GROUP	VARCHAR2(32)	Name of the session's current resource consumer group
PDML_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML.
PDDL_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL.
PQ_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL QUERY enabled mode. If DISABLED, PARALLEL QUERY enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL QUERY.
CURRENT_QUEUE_DURATION	NUMBER	If queued (1), the current amount of time the session has been queued. If not currently queued, the value is 0.
CLIENT_IDENTIFIER	VARCHAR2(64)	Client identifier of the session
BLOCKING_SESSION_STATUS	VARCHAR2(11)	Blocking session status: VALID NO HOLDER GLOBAL NOT IN WAIT UNKNOWN
BLOCKING_INSTANCE	NUMBER	Instance identifier of blocking session
BLOCKING_SESSION	NUMBER	Session identifier of blocking session
SEQ#	NUMBER	Sequence number that uniquely identifies the wait. Incremented for each wait.
EVENT#	NUMBER	Event number
EVENT	VARCHAR2(64)	Resource or event for which the session is waiting See Also: Appendix C, "Oracle Wait Events"
P1TEXT	VARCHAR2(64)	Description of the first additional parameter
P1	NUMBER	First additional parameter
P1RAW	RAW(4)	First additional parameter
P2TEXT	VARCHAR2(64)	Description of the second additional parameter
P2	NUMBER	Second additional parameter
P2RAW	RAW(4)	Second additional parameter
P3TEXT	VARCHAR2(64)	Description of the third additional parameter
P3	NUMBER	Third additional parameter
P3RAW	RAW(4)	Third additional parameter
WAIT_CLASS_ID	NUMBER	Identifier of the wait class
WAIT_CLASS#	NUMBER	Number of the wait class
WAIT_CLASS	VARCHAR2(64)	Name of the wait class
WAIT_TIME	NUMBER	A nonzero value is the session's last wait time. A zero value means the session is currently waiting.
SECONDS_IN_WAIT	NUMBER	If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is the active seconds since the last wait ended.
STATE	VARCHAR2(19)	Wait state: 0 - WAITING (the session is currently waiting) -2 - WAITED UNKNOWN TIME (duration of last wait is unknown) -1 - WAITED SHORT TIME (last wait <1/100th of a second) >0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
SERVICE_NAME	VARCHAR2(64)	Service name of the session
SQL_TRACE	VARCHAR2(8)	Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)
SQL_TRACE_WAITS	VARCHAR2(5)	Indicates whether wait tracing is enabled (TRUE) or not (FALSE)
SQL_TRACE_BINDS	VARCHAR2(5)	Indicates whether bind tracing is enabled (TRUE) or not (FALSE)

V$SESSION_WAIT_HISTORY

V$SESSION_WAIT_HISTORY displays the last 10 wait events for each active session.

Column	Datatype	Description
`SID`	`NUMBER`	`Session identifier`
`SEQ#`	`NUMBER`	`Sequence of wait events; 1 is the most recent`
`EVENT#`	`NUMBER`	`Event number`
`EVENT`	`VARCHAR2(64)`	`Resource or event for which the session is waiting`
`P1TEXT`	`VARCHAR2(64)`	`Description of the first additional parameter`
`P1`	`NUMBER`	`First additional parameter`
`P2TEXT`	`VARCHAR2(64)`	`Description of the second additional parameter`
`P2`	`NUMBER`	`Second additional parameter`
`P3TEXT`	`VARCHAR2(64)`	`Description of the third additional parameter`
`P3`	`NUMBER`	`Third additional parameter`
`WAIT_TIME`	`NUMBER`	`A nonzero value is the session's last wait time. A zero value means the session is currently waiting.`

V$SESSION_WAIT_HISTORY

V$SESSION_WAIT_HISTORY displays the last 10 wait events for each active session.

Column	Datatype	Description
`SID`	`NUMBER`	`Session identifier`
`SEQ#`	`NUMBER`	`Sequence of wait events; 1 is the most recent`
`EVENT#`	`NUMBER`	`Event number`
`EVENT`	`VARCHAR2(64)`	`Resource or event for which the session is waiting`
`P1TEXT`	`VARCHAR2(64)`	`Description of the first additional parameter`
`P1`	`NUMBER`	`First additional parameter`
`P2TEXT`	`VARCHAR2(64)`	`Description of the second additional parameter`
`P2`	`NUMBER`	`Second additional parameter`
`P3TEXT`	`VARCHAR2(64)`	`Description of the third additional parameter`
`P3`	`NUMBER`	`Third additional parameter`
`WAIT_TIME`	`NUMBER`	`A nonzero value is the session's last wait time. A zero value means the session is currently waiting.`

V$LOCK

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

Column	Datatype	Description
`ADDR`	`RAW(4 \| 8)`	`Address of lock state object`
`KADDR`	`RAW(4 \| 8)`	`Address of lock`
`SID`	`NUMBER`	`Identifier for session holding or acquiring the lock`
`TYPE`	`VARCHAR2(2)`	`Type of user or system lock` `The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:` `TM - DML enqueue` `TX - Transaction enqueue` `UL - User supplied` `The locks on the system types are held for extremely short periods of time. The system type locks are listed in` `Table 6-1.`
`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`	`A value of either 0 or 1, depending on whether or not the lock in question is the blocker.`

V$LATCH

V$LATCH shows aggregate latch statistics for both parent and child latches, grouped by latch name. Individual parent and child latch statistics are broken down in the views V$LATCH_PARENT and V$LATCH_CHILDREN.

Column	Datatype	Description
`ADDR`	`RAW(4 \| 8)`	`Address of the latch object`
`LATCH#`	`NUMBER`	`Latch number`
`LEVEL#`	`NUMBER`	`Latch level`
`NAME`	`VARCHAR2(50)`	`Latch name`
`HASH`	`NUMBER`	`Latch hash`
`GETS`	`NUMBER`	`Number of times the latch was requested in willing-to-wait mode`
`MISSES`	`NUMBER`	`Number of times the latch was requested in willing-to-wait mode and the requestor had to wait`
`SLEEPS`	`NUMBER`	`Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch`
`IMMEDIATE_GETS`	`NUMBER`	`Number of times a latch was requested in no-wait mode`
`IMMEDIATE_MISSES`	`NUMBER`	`Number of times a no-wait latch request did not succeed (that is, missed)`
`WAITERS_WOKEN`	`NUMBER`	`This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero.`
`WAITS_HOLDING_LATCH`	`NUMBER`	`This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero.`
`SPIN_GETS`	`NUMBER`	`Willing-to-wait latch requests which missed the first try but succeeded while spinning`
`SLEEP[1 \| 2 \| 3]`	`NUMBER`	`These columns have been deprecated and are present only for compatibility with previous releases of Oracle. No data is accumulated for these columns; they will always have a value of zero. As a substitute for this column you can query the appropriate rows of theV$EVENT_HISTOGRAM view where the EVENT column has a value of latch free or latch:%.`
`SLEEP4`	`NUMBER`	`This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero. As a substitute for this column you can query the appropriate rows of theV$EVENT_HISTOGRAM view where the EVENT column has a value of latch free or latch:%.`
`SLEEP[5 \| 6 \| 7 \| 8 \| 9 \| 10 \| 11]`	`NUMBER`	`These columns have been deprecated and are present only for compatibility with previous releases of Oracle. No data is accumulated for these columns.`
`WAIT_TIME`	`NUMBER`	`Elapsed time spent waiting for the latch (in microseconds)`

V$LATCH_CHILDREN

V$LATCH_CHILDREN 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.

Column	Datatype	Description
`ADDR`	`RAW(4 \| 8)`	`Address of the latch object`
`LATCH#`	`NUMBER`	`Latch number of the parent latch`
`CHILD#`	`NUMBER`	`Child latch number (unique only to each parent latch)`
`LEVEL#`	`NUMBER`	`Latch level`
`NAME`	`VARCHAR2(50)`	`Latch name`
`HASH`	`NUMBER`	`Latch hash`
`GETS`	`NUMBER`	`Number of times the latch was requested in willing-to-wait mode`
`MISSES`	`NUMBER`	`Number of times the latch was requested in willing-to-wait mode and the requestor had to wait`
`SLEEPS`	`NUMBER`	`Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch`
`IMMEDIATE_GETS`	`NUMBER`	`Number of times a latch was requested in no-wait mode`
`IMMEDIATE_MISSES`	`NUMBER`	`Number of times a no-wait latch request did not succeed (that is, missed)`
`WAITERS_WOKEN`	`NUMBER`	`This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero.`
`WAITS_HOLDING_LATCH`	`NUMBER`	`This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero.`
`SPIN_GETS`	`NUMBER`	`Willing-to-wait latch requests which missed the first try but succeeded while spinning`
`SLEEP[1 \| 2 \| 3]`	`NUMBER`	`These columns have been deprecated and are present only for compatibility with previous releases of Oracle. No data is accumulated for these columns; they will always have a value of zero. As a substitute for these columns you can query the appropriate rows of theV$EVENT_HISTOGRAM view where the EVENT column has a value of latch free or latch:%.`
`SLEEP4`	`NUMBER`	`This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero. As a substitute for this column you can query the appropriate rows of theV$EVENT_HISTOGRAM view where the EVENT column has a value of latch free or latch:%.`
`SLEEP[5 \| 6 \| 7 \| 8 \| 9 \| 10 \| 11]`	`NUMBER`	`These columns have been deprecated and are present only for compatibility with previous releases of Oracle. No data is accumulated for these columns.`
`WAIT_TIME`	`NUMBER`	`Elapsed time spent waiting for the latch (in microseconds)`

V$LOCK

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

Column	Datatype	Description
`ADDR`	`RAW(4 \| 8)`	`Address of lock state object`
`KADDR`	`RAW(4 \| 8)`	`Address of lock`
`SID`	`NUMBER`	`Identifier for session holding or acquiring the lock`
`TYPE`	`VARCHAR2(2)`	`Type of user or system lock` `The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:` `TM - DML enqueue` `TX - Transaction enqueue` `UL - User supplied` `The locks on the system types are held for extremely short periods of time. The system type locks are listed in` `Table 6-1.`
`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`	`A value of either 0 or 1, depending on whether or not the lock in question is the blocker.`

DBA_WAITERS

DBA_WAITERS shows all the sessions that are waiting for a lock.

Column	Datatype	NULL	Description
`WAITING_SESSION`	`NUMBER`		`The waiting session`
`HOLDING_SESSION`	`NUMBER`		`The holding session`
`LOCK_TYPE`	`VARCHAR2(26)`		`The lock type`
`MODE_HELD`	`VARCHAR2(40)`		`The mode held`
`MODE_REQUESTED`	`VARCHAR2(40)`		`The mode requested`
`LOCK_ID1`	`VARCHAR2(40)`		`Lock ID 1`
`LOCK_ID2`	`VARCHAR2(40)`		`Lock ID 2`

DBA_BLOCKERS

DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.

Column	Datatype	NULL	Description
`HOLDING_SESSION`	`NUMBER`		`Session holding a lock`

V$SHARED_POOL_RESERVED

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

Column	Datatype	Description
`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.` `See Also:` `"SHARED_POOL_RESERVED_SIZE"`
`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.`
`REQUEST_FAILURES`	`NUMBER`	`Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-04031 occurred)`
`LAST_FAILURE_SIZE`	`NUMBER`	`Request size of the last failed request (that is, the request size for the last ORA-04031 error)`
`ABORTED_REQUEST_THRESHOLD`	`NUMBER`	`Minimum size of a request which signals an ORA-04031 error without flushing objects`
`ABORTED_REQUESTS`	`NUMBER`	`Number of requests that signalled an ORA-04031 error without flushing objects`
`LAST_ABORTED_SIZE`	`NUMBER`	`Last size of the request that returned an ORA-04031 error without flushing objects from the LRU list`

V$SGA_RESIZE_OPS

V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include in-progress operations. All sizes are expressed in bytes.

Column	Datatype	Description
COMPONENT	VARCHAR2(64)	Component name
OPER_TYPE	VARCHAR2(13)	Operation type: STATIC INITIALIZING DISABLED GROW SHRINK SHRINK_CANCEL
OPER_MODE	VARCHAR2(9)	Operation mode: MANUAL DEFERRED IMMEDIATE
PARAMETER	VARCHAR2(80)	Name of the parameter for the resize operation
INITIAL_SIZE	NUMBER	Parameter value at the start of the operation
TARGET_SIZE	NUMBER	Requested value of the parameter after the resize
FINAL_SIZE	NUMBER	Real value of the parameter after the resize
STATUS	VARCHAR2(9)	Completion status of the operation: INACTIVE PENDING COMPLETE CANCELLED ERROR
START_TIME	DATE	Start time of the operation
END_TIME	DATE	End time of the operation

DB_CACHE_ADVICE

Property	Description
Parameter type	String
Syntax	DB_CACHE_ADVICE = { ON \| READY \| OFF }
Default value	If STATISTICS_LEVEL is set to TYPICAL or ALL, then ON If STATISTICS_LEVEL is set to BASIC, then OFF
Modifiable	ALTER SYSTEM
Basic	No

DB_CACHE_ADVICE enables or disables statistics gathering used for predicting behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view.

Values:

OFF

Advisory is turned off and the memory for the advisory is not allocated.

READY

Advisory is turned off but the memory for the advisory remains allocated. Allocating the memory before the advisory is actually turned on avoids the risk of an error when you switch the parameter to ON.

If the parameter is switched to this state from ON, the contents of the view are preserved and the memory for the advisory is retained.

If the parameter is switched to this state from OFF, you may get an error.

Advisory is turned on. CPU and memory overheads are incurred. Attempting to set the parameter to this state when it is already in the OFF state may result in an error. Otherwise, the view (V$DB_CACHE_ADVICE) is reset and statistics are gathered to the newly refreshed view.

If the parameter is in the READY state, you can set it to ON without any errors because the memory is already allocated. The view is reset and statistics are displayed in the newly refreshed view.

DBA_EXTENTS

DBA_EXTENTS describes the extents comprising the segments in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.

Related View

USER_EXTENTS describes the extents comprising the segments owned by the current user's objects. This view does not display the OWNER, FILE_ID, BLOCK_ID, or RELATIVE_FNO columns.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`		`Owner of the segment associated with the extent`
`SEGMENT_NAME`	`VARCHAR2(81)`		`Name of the segment associated with the extent`
`PARTITION_NAME`	`VARCHAR2(30)`		`Object Partition Name (Set to NULL for non-partitioned objects)`
`SEGMENT_TYPE`	`VARCHAR2(18)`		`Type of the segment: INDEX PARTITION, TABLE PARTITION`
`TABLESPACE_NAME`	`VARCHAR2(30)`		`Name of the tablespace containing the extent`
`EXTENT_ID`	`NUMBER`		`Extent number in the segment`
`FILE_ID`	`NUMBER`		`File identifier number of the file containing the extent`
`BLOCK_ID`	`NUMBER`		`Starting block number of the extent`
`BYTES`	`NUMBER`		`Size of the extent in bytes`
`BLOCKS`	`NUMBER`		`Size of the extent in Oracle blocks`
`RELATIVE_FNO`	`NUMBER`		`Relative file number of the first extent block`

V$PGASTAT

V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.

Column	Datatype	Description
`NAME`	`VARCHAR2(64)`	`Name of the statistic (see` `Table 7-2)`
`VALUE`	`NUMBER`	`Statistic value`
`UNIT`	`VARCHAR2(12)`	`Unit for the value (microseconds, bytes, or percent)`

Table 7-2 V$PGASTAT Statistics

Statistic Name	Description
`aggregate PGA target parameter`	`Current value of the PGA_AGGREGATE_TARGET initialization parameter. If this parameter is not set, then its value is 0 and automatic management of PGA memory is disabled.`
`aggregate PGA auto target`	`Amount of PGA memory the Oracle Database can use for work areas running in automatic mode. This amount is dynamically derived from the value of thePGA_AGGREGATE_TARGET initialization parameter and the current work area workload, and continuously adjusted by the Oracle Database.` `If this value is small compared to the value of PGA_AGGREGATE_TARGET, then a large amount of PGA memory is used by other components of the system (for example, PL/SQL or Java memory) and little is left for work areas. The DBA must ensure that enough PGA memory is left for work areas running in automatic mode.`
`global memory bound`	`Maximum size of a work area executed in automatic mode. This value is continuously adjusted by the Oracle Database to reflect the current state of the work area workload. The global memory bound generally decreases when the number of active work areas is increasing in the system.` `If the value of the global bound decreases below 1 MB, then the value of PGA_AGGREGATE_TARGET should be increased.`
`total PGA allocated`	`Current amount of PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGETinitialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value.`
`total PGA used`	`Indicates how much PGA memory is currently consumed by work areas. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).`
`total PGA used for auto workareas`	`Indicates how much PGA memory is currently consumed by work areas running under the automatic memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).`
`total PGA used for manual workareas`	`Indicates how much PGA memory is currently consumed by work areas running under the manual memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).`
`over allocation count`	`This statistic is cumulative since instance startup. Over allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small. When this happens, the Oracle Database cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated.` `If over allocation occurs, then increase the value of PGA_AGGREGATE_TARGET using the information provided by the V$PGA_TARGET_ADVICE view.`
`bytes processed`	`Number of bytes processed by memory intensive SQL operators, cumulated since instance startup.`
`extra bytes read/written`	`Number of bytes processed during extra passes of the input data, cumulated since instance startup. When a work area cannot run optimal, one or more of these extra passes is performed.`
`cache hit percentage`	`A metric computed by the Oracle Database to reflect the performance of the PGA memory component, cumulative since instance startup. A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.` `When a work area cannot run optimal, one or more extra passes is performed over the input data. This will reduce the cache hit percentage in proportion to the size of the input data and the number of extra passes performed.`

V$PGA_TARGET_ADVICE

V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated if the STATISTICS_LEVEL parameter is set to BASIC. Base statistics for this view are reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

Column	Datatype	Description
`PGA_TARGET_FOR_ESTIMATE`	`NUMBER`	`Value of PGA_AGGREGATE_TARGET for this prediction (in bytes)`
`PGA_TARGET_FACTOR`	`NUMBER`	`PGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameter`
`ADVICE_STATUS`	`VARCHAR2(3)`	`Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameter`
`BYTES_PROCESSED`	`NUMBER`	`Total bytes processed by all the work areas considered by this advice (in bytes)`
`ESTD_EXTRA_BYTES_RW`	`NUMBER`	`Estimated number of extra bytes which would be read or written if PGA_AGGREGATE_TARGET was set to the value of thePGA_TARGET_FOR_ESTIMATE column. This number is derived from the estimated number and size of work areas which would run in one-pass (or multi-pass) for that value of PGA_AGGREGATE_TARGET.`
`ESTD_PGA_CACHE_HIT_PERCENTAGE`	`NUMBER`	`Estimated value of the cache hit percentage statistic when PGA_AGGREGATE_TARGET equals PGA_TARGET_FOR_ESTIMATE. This column is derived from the above two columns and is equal to BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)`
`ESTD_OVERALLOC_COUNT`	`NUMBER`	`Estimated number of PGA memory over-allocations if the value of PGA_AGGREGATE_TARGET is set to PGA_TARGET_FOR_ESTIMATE. A nonzero value means that PGA_TARGET_FOR_ESTIMATE is not large enough to run the work area workload. Hence, the DBA should not setPGA_AGGREGATE_TARGET to PGA_TARGET_FOR_ESTIMATE since Oracle will not be able to honor that target.`

V$SQL_WORKAREA_HISTOGRAM

V$SQL_WORKAREA_HISTOGRAM displays the cumulative work area execution statistics (cumulated since instance startup) for different work area groups. The work areas are split into 33 groups based on their optimal memory requirements with the requirements increasing in powers of two. That is, work areas whose optimal requirement varies from 0 KB to 1 KB, 1 KB to 2 KB, 2 KB to 4 KB, ... and 2 TB to 4 TB.

For each work area group, the V$SQL_WORKAREA_HISTOGRAM view shows how many work areas in that group were able to run in optimal mode, how many were able to run in one-pass mode, and finally how many ran in multi-pass mode. The DBA can take a snapshot at the beginning and the end of a desired time interval to derive the same statistics for that interval.

Column	Datatype	Description
`LOW_OPTIMAL_SIZE`	`NUMBER`	`Lower bound for the optimal memory requirement of work areas included in this row (bytes)`
`HIGH_OPTIMAL_SIZE`	`NUMBER`	`Upper bound for the optimal memory requirement of work areas included in this row (bytes)`
`OPTIMAL_EXECUTIONS`	`NUMBER`	`Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which have been executed in optimal mode since instance startup`
`ONEPASS_EXECUTIONS`	`NUMBER`	`Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which have been executed in one-pass mode since instance startup`
`MULTIPASSES_EXECUTIONS`	`NUMBER`	`Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which have been executed in multi-pass mode since instance startup`
`TOTAL_EXECUTIONS`	`NUMBER`	`Sum of OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, and MULTIPASSES_EXECUTIONS`

V$SQL_WORKAREA

V$SQL_WORKAREA displays information about work areas used by SQL cursors. Each SQL statement stored in the shared pool has one or more child cursors that are listed in the V$SQL view. V$SQL_WORKAREA lists all work areas needed by these child cursors; V$SQL_WORKAREA can be joined with V$SQLAREA on (ADDRESS, HASH_VALUE) and with V$SQL on (ADDRESS, HASH_VALUE, CHILD_NUMBER).

You can use this view to find out answers to the following questions:

What are the top 10 work areas that require the most cache area?

For work areas allocated in AUTO mode, what percentage of work areas are running using maximum memory?

Column	Datatype	Description
`ADDRESS`	`RAW(4 \| 8)`	`Address of the parent cursor handle`
`HASH_VALUE`	`NUMBER`	`Hash value of the parent statement in the library cache. Two columns PARENT_HANDLE and HASH_VALUE can be used to join withV$SQLAREA to locate the parent cursor.`
`SQL_ID`	`VARCHAR2(13)`	`SQL identifier of the parent statement in the library cache`
`CHILD_NUMBER`	`NUMBER`	`Number of the child cursor that uses this work area. The columns PARENT_HANDLE, HASH_VALUE, and CHILD_NUMBER can be used to join with V$SQL to locate the child cursor using this area.`
`WORKAREA_ADDRESS`	`RAW(4 \| 8)`	`Address of the work area handle. This is the primary key for the view.`
`OPERATION_TYPE`	`VARCHAR2(20)`	`Type of operation using the work area (SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, or BITMAP CREATE)`
`OPERATION_ID`	`NUMBER`	`A unique number used to identify the operation in the execution plan. This identifier can be joined to V$SQL_PLAN to locate the operation that uses this work area.`
`POLICY`	`VARCHAR2(10)`	`Sizing policy for this work area (MANUAL or AUTO)`
`ESTIMATED_OPTIMAL_SIZE`	`NUMBER`	`Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). Derived from either optimizer statistics or previous executions.`
`ESTIMATED_ONEPASS_SIZE`	`NUMBER`	`Estimated size (in KB) required by this work area to execute the operation in a single pass. Derived from either optimizer statistics or previous executions.`
`LAST_MEMORY_USED`	`NUMBER`	`Memory (in KB) used by this work area during the last execution of the cursor`
`LAST_EXECUTION`	`VARCHAR2(10)`	`Indicates whether this work area runs using OPTIMAL, ONE PASS, or ONE PASS memory requirement (or MULTI-PASS), during the last execution of the cursor`
`LAST_DEGREE`	`NUMBER`	`Degree of parallelism used during the last execution of this operation`
`TOTAL_EXECUTIONS`	`NUMBER`	`Number of times this work area was active`
`OPTIMAL_EXECUTIONS`	`NUMBER`	`Number of times this work area ran in optimal mode`
`ONEPASS_EXECUTIONS`	`NUMBER`	`Number of times this work area ran in one-pass mode`
`MULTIPASSES_EXECUTIONS`	`NUMBER`	`Number of times this work area ran below the one-pass memory requirement`
`ACTIVE_TIME`	`NUMBER`	`Average time this work area is active (in hundredths of a second)`
`MAX_TEMPSEG_SIZE`	`NUMBER`	`Maximum temporary segment size (in bytes) created by an instantiation of this work area. This column is null if this work area has never spilled to disk.`
`LAST_TEMPSEG_SIZE`	`NUMBER`	`Temporary segment size (in bytes) created in the last instantiation of this work area. This column is null if the last instantiation of this work area did not spill to disk.`

V$SQL_WORKAREA_ACTIVE

V$SQL_WORKAREA_ACTIVE contains an instantaneous view of the work areas currently allocated by the system. You can join this view against V$SQL_WORKAREA on WORKAREA_ADDRESS to access the definition of that work area. If a work area spills to disk, then this view contains information for the temporary segment created on behalf of this work area.

The last three columns are included to enable joining V$SQL_WORKAREA_ACTIVE with V$TEMPSEG_USAGE to retrieve more information on this temporary segment.

You can use this view to answer the following:

What are the top 10 largest work areas currently allocated in my system?

What percentage of memory is over-allocated (EXPECTED_SIZE < ACTUAL_MEM_USED) and under-allocated (EXPECTED_SIZE > ACTUAL_MEM_USED)?

What are the active work areas using more memory than what is expected by the memory manager?

What are the active work areas that have spilled to disk?

Column	Datatype	Description
`SQL_HASH_VALUE`	`NUMBER`	`Hash value of the SQL statement that is currently being executed`
`SQL_ID`	`VARCHAR2(13)`	`SQL identifier of the SQL statement that is currently being executed`
`WORKAREA_ADDRESS`	`RAW(4 \| 8)`	`Address of the work area handle. This is the primary key for the view.`
`OPERATION_TYPE`	`VARCHAR2(20)`	`Type of operation using the work area (SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, or BITMAP CREATE)`
`OPERATION_ID`	`NUMBER`	`A unique number used to identify the operation in the execution plan. This identifier can be joined to V$SQL_PLAN to locate the operation that uses this work area.`
`POLICY`	`VARCHAR2(6)`	`Sizing policy for this work area (MANUAL or AUTO)`
`SID`	`NUMBER`	`Session identifier`
`QCINST_ID`	`NUMBER`	`Query coordinator instance identifier. Along with QCSID, enables you to uniquely identify the query coordinator.`
`QCSID`	`NUMBER`	`Query coordinator session identifier. This is the same as the SID if the work area is allocated by a serial cursor.`
`ACTIVE_TIME`	`NUMBER`	`Average time this work area is active (in centi-seconds)`
`WORK_AREA_SIZE`	`NUMBER`	`Maximum size of the work area as it is currently used by the operation`
`EXPECTED_SIZE`	`NUMBER`	`Expected size (in KB) for this work area. EXPECTED_SIZE is set on behalf of the operation by the memory manager. Memory can be over-allocated when WORK_AREA_SIZE has a higher value than EXPECTED_SIZE. This can occur when the operation using this work area takes a long time to resize it.`
`ACTUAL_MEM_USED`	`NUMBER`	`Amount of PGA memory (in KB) currently allocated on behalf of this work area. This value should range between 0 andWORK_AREA_SIZE.`
`MAX_MEM_USED`	`NUMBER`	`Maximum memory amount (in KB) used by this work area`
`NUMBER_PASSES`	`NUMBER`	`Number of passes corresponding to this work area (0 if running in OPTIMAL mode)`
`TEMPSEG_SIZE`	`NUMBER`	`Size (in bytes) of the temporary segment used on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.`
`TABLESPACE`	`VARCHAR2(31)`	`Tablespace name for the temporary segment created on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.`
`SEGRFNO#`	`NUMBER`	`Relative file number within the tablespace for the temporary segment created on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.`
`SEGBLK#`	`NUMBER`	`Block number for the temporary segment created on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.`

V$SQL

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

Column	Datatype	Description
SQL_TEXT	VARCHAR2(1000)	First thousand characters of the SQL text for the current cursor
SQL_FULLTEXT	CLOB	Full text for the SQL statement exposed as a CLOB column. The full text of a SQL statement can be retrieved using this column instead of joining with the V$SQL_TEXT dynamic performance view.
SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
SHARABLE_MEM	NUMBER	Amount of shared memory used by the child cursor (in bytes)
PERSISTENT_MEM	NUMBER	Fixed amount of memory used for the lifetime of the child cursor (in bytes)
RUNTIME_MEM	NUMBER	Fixed amount of memory required during the execution of the child cursor
SORTS	NUMBER	Number of sorts that were done for the child cursor
LOADED_VERSIONS	NUMBER	Indicates whether the context heap is loaded (1) or not (0)
OPEN_VERSIONS	NUMBER	Indicates whether the child cursor is locked (1) or not (0)
USERS_OPENING	NUMBER	Number of users executing the statement
FETCHES	NUMBER	Number of fetches associated with the SQL statement
EXECUTIONS	NUMBER	Number of executions that took place on this object since it was brought into the library cache
PX_SERVERS_EXECUTIONS	NUMBER	Total number of executions performed by Parallel eXecution Servers. The value is 0 when the statement has never been executed in parallel.
END_OF_FETCH_COUNT	NUMBER	Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNTcolumn should be less or equal to the value of the EXECUTIONS column.
USERS_EXECUTING	NUMBER	Number of users executing the statement
LOADS	NUMBER	Number of times the object was either loaded or reloaded
FIRST_LOAD_TIME	VARCHAR2(19)	Timestamp of the parent creation time
INVALIDATIONS	NUMBER	Number of times this child cursor has been invalidated
PARSE_CALLS	NUMBER	Number of parse calls for this child cursor
DISK_READS	NUMBER	Number of disk reads for this child cursor
DIRECT_WRITES	NUMBER	Number of direct writes for this child cursor
BUFFER_GETS	NUMBER	Number of buffer gets for this child cursor
APPLICATION_WAIT_TIME	NUMBER	Application wait time (in microseconds)
CONCURRENCY_WAIT_TIME	NUMBER	Concurrency wait time (in microseconds)
CLUSTER_WAIT_TIME	NUMBER	Cluster wait time (in microseconds)
USER_IO_WAIT_TIME	NUMBER	User I/O Wait Time (in microseconds)
PLSQL_EXEC_TIME	NUMBER	PL/SQL execution time (in microseconds)
JAVA_EXEC_TIME	NUMBER	Java execution time (in microseconds)
ROWS_PROCESSED	NUMBER	Total number of rows the parsed SQL statement returns
COMMAND_TYPE	NUMBER	Oracle command type definition
OPTIMIZER_MODE	VARCHAR2(10)	Mode under which the SQL statement is executed
OPTIMIZER_COST	NUMBER	Cost of this query given by the optimizer
OPTIMIZER_ENV	RAW(691)	Optimizer environment
OPTIMIZER_ENV_HASH_VALUE	NUMBER	Hash value for the optimizer environment
PARSING_USER_ID	NUMBER	User ID of the user who originally built this child cursor
PARSING_SCHEMA_ID	NUMBER	Schema ID that was used to originally build this child cursor
PARSING_SCHEMA_NAME	VARCHAR2(30)	Schema name that was used to originally build this child cursor
KEPT_VERSIONS	NUMBER	Indicates whether this child cursor has been marked to be kept pinned in the cache using the DBMS_SHARED_POOL package
ADDRESS	RAW(4 \| 8)	Address of the handle to the parent for this cursor
TYPE_CHK_HEAP	RAW(4)	Descriptor of the type check heap for this child cursor
HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
OLD_HASH_VALUE	NUMBER	Old SQL hash value
PLAN_HASH_VALUE	NUMBER	Numerical representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
CHILD_NUMBER	NUMBER	Number of this child cursor
SERVICE	VARCHAR2(64)	Service name
SERVICE_HASH	NUMBER	Hash value for the name listed in SERVICE
MODULE	VARCHAR2(64)	Contains the name of the module that was executing at the time that the SQL statement was first parsed, which is set by callingDBMS_APPLICATION_INFO.SET_MODULE
MODULE_HASH	NUMBER	Hash value of the module listed 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, which is set by callingDBMS_APPLICATION_INFO.SET_ACTION
ACTION_HASH	NUMBER	Hash value of the action listed in the ACTION column
SERIALIZABLE_ABORTS	NUMBER	Number of times the transaction fails to serialize, producing ORA-08177 errors, per cursor
OUTLINE_CATEGORY	VARCHAR2(64)	If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank.
CPU_TIME	NUMBER	CPU time (in microseconds) used by this cursor for parsing, executing, and fetching
ELAPSED_TIME	NUMBER	Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
OUTLINE_SID	NUMBER	Outline session identifier
CHILD_ADDRESS	RAW(4 \| 8)	Address of the child cursor
SQLTYPE	NUMBER	Denotes the version of the SQL language used for this statement
REMOTE	VARCHAR2(1)	Indicates whether the cursor is remote mapped (Y) or not (N)
OBJECT_STATUS	VARCHAR2(19)	Status of the cursor: VALID - Valid, authorized without errors VALID_AUTH_ERROR - Valid, authorized with authorization errors VALID_COMPILE_ERROR - Valid, authorized with compilation errors VALID_UNAUTH - Valid, unauthorized INVALID_UNAUTH - Invalid, unauthorized INVALID - Invalid, unauthorized but keep the timestamp
LITERAL_HASH_VALUE	NUMBER	Hash value of the literals which are replaced with system-generated bind variables and are to be matched, when CURSOR_SHARING is used. This is not the hash value for the SQL statement. If CURSOR_SHARING is not used, then the value is 0.
LAST_LOAD_TIME	VARCHAR2(19)	Time at which the query plan (heap 6) was loaded into the library cache
IS_OBSOLETE	VARCHAR2(1)	Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large.
CHILD_LATCH	NUMBER	Child latch number that is protecting the cursor
SQL_PROFILE	VARCHAR2(64)	SQL profile
PROGRAM_ID	NUMBER	Program identifier
PROGRAM_LINE#	NUMBER	Program line number
EXACT_MATCHING_SIGNATURE	NUMBER	Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
FORCE_MATCHING_SIGNATURE	NUMBER	The signature used when the CURSOR_SHARING parameter is set to FORCE
LAST_ACTIVE_TIME	DATE	TIme at which the query plan was last active
BIND_DATA	RAW(2000)	Bind data

V$SQLAREA

V$SQLAREA 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)	First thousand characters of the SQL text for the current cursor
SQL_FULLTEXT	CLOB	All characters of the SQL text for the current cursor
SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
SHARABLE_MEM	NUMBER	Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors.
PERSISTENT_MEM	NUMBER	Fixed amount of memory used for the lifetime of an open cursor. If multiple child cursors exist, the fixed sum of memory used for the lifetime of all the child cursors.
RUNTIME_MEM	NUMBER	Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, the fixed sum of all memory required during execution of all the child cursors.
SORTS	NUMBER	Sum of the number of sorts that were done for all the child cursors
VERSION_COUNT	NUMBER	Number of child cursors that are present in the cache under this parent
LOADED_VERSIONS	NUMBER	Number of child cursors 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	Number of users that have any of the child cursors open
FETCHES	NUMBER	Number of fetches associated with the SQL statement
EXECUTIONS	NUMBER	Total number of executions, totalled over all the child cursors
PX_SERVERS_EXECUTIONS	NUMBER	Total number of executions performed by Parallel eXecution Servers. The value is 0 when the statement has never been executed in parallel.
END_OF_FETCH_COUNT	NUMBER	Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNTcolumn should be less or equal to the value of the EXECUTIONS column.
USERS_EXECUTING	NUMBER	Total number of users executing the statement over all child cursors
LOADS	NUMBER	Number of times the object was loaded or reloaded
FIRST_LOAD_TIME	VARCHAR2(19)	Timestamp of the parent creation time
INVALIDATIONS	NUMBER	Total number of invalidations over all the child cursors
PARSE_CALLS	NUMBER	Sum of all parse calls to all the child cursors under this parent
DISK_READS	NUMBER	Sum of the number of disk reads over all child cursors
DIRECT_WRITES	NUMBER	Sum of the number of direct writes over all child cursors
BUFFER_GETS	NUMBER	Sum of buffer gets over all child cursors
APPLICATION_WAIT_TIME	NUMBER	Application wait time (in microseconds)
CONCURRENCY_WAIT_TIME	NUMBER	Concurrency wait time (in microseconds)
CLUSTER_WAIT_TIME	NUMBER	Cluster wait time (in microseconds)
USER_IO_WAIT_TIME	NUMBER	User I/O Wait Time (in microseconds)
PLSQL_EXEC_TIME	NUMBER	PL/SQL execution time (in microseconds)
JAVA_EXEC_TIME	NUMBER	Java execution time (in microseconds)
ROWS_PROCESSED	NUMBER	Total number of rows processed on behalf of this SQL statement
COMMAND_TYPE	NUMBER	Oracle command type definition
OPTIMIZER_MODE	VARCHAR2(10)	Mode under which the SQL statement was executed
OPTIMIZER_COST	NUMBER	Cost of this query given by the optimizer
OPTIMIZER_ENV	RAW(703)	Optimizer environment
OPTIMIZER_ENV_HASH_VALUE	NUMBER	Hash value for the optimizer environment
PARSING_USER_ID	NUMBER	User ID of the user that has parsed the very first cursor under this parent
PARSING_SCHEMA_ID	NUMBER	Schema ID that was used to parse this child cursor
PARSING_SCHEMA_NAME	VARCHAR2(30)	Schema name that was used to parse this child cursor
KEPT_VERSIONS	NUMBER	Number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package
ADDRESS	RAW(4 \| 8)	Address of the handle to the parent for this cursor
HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
OLD_HASH_VALUE	NUMBER	Old SQL hash value
PLAN_HASH_VALUE	NUMBER	Numerical representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line)
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 callingDBMS_APPLICATION_INFO.SET_MODULE
MODULE_HASH	NUMBER	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 callingDBMS_APPLICATION_INFO.SET_ACTION
ACTION_HASH	NUMBER	Hash value of the action that is named in the ACTION column
SERIALIZABLE_ABORTS	NUMBER	Number of times the transaction fails to serialize, producing ORA-08177 errors, totalled over all the child cursors
OUTLINE_CATEGORY	VARCHAR2(64)	If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank.
CPU_TIME	NUMBER	CPU time (in microseconds) used by this cursor for parsing, executing, and fetching
ELAPSED_TIME	NUMBER	Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
OUTLINE_SID	VARCHAR2(40)	Outline session identifier
LAST_ACTIVE_CHILD_ADDRESS	RAW(4)	Address (identifier) of the child cursor that was the last to be active in the group (that is, the child cursor on behalf of which statistics in V$SQL were updated)
REMOTE	VARCHAR2(1)	Indicates whether the cursor is remote mapped (Y) or not (N)
OBJECT_STATUS	VARCHAR2(19)	Status of the cursor: VALID - Valid, authorized without errors VALID_AUTH_ERROR - Valid, authorized with authorization errors VALID_COMPILE_ERROR - Valid, authorized with compilation errors VALID_UNAUTH - Valid, unauthorized INVALID_UNAUTH - Invalid, unauthorized INVALID - Invalid, unauthorized but keep the timestamp
LITERAL_HASH_VALUE	NUMBER	Hash value of the literals which are replaced with system-generated bind variables and are to be matched, when CURSOR_SHARING is used. This is not the hash value for the SQL statement. If CURSOR_SHARING is not used, then the value is 0.
LAST_LOAD_TIME	DATE	Time at which the query plan (heap 6) was loaded into the library cache
IS_OBSOLETE	VARCHAR2(1)	Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large.
CHILD_LATCH	NUMBER	Child latch number that is protecting the cursor
SQL_PROFILE	VARCHAR2(64)	SQL profile
PROGRAM_ID	NUMBER	Program identifier
PROGRAM_LINE#	NUMBER	Program line number
EXACT_MATCHING_SIGNATURE	NUMBER	The signature used when the CURSOR_SHARING parameter is set to EXACT
FORCE_MATCHING_SIGNATURE	NUMBER	The signature used when the CURSOR_SHARING parameter is set to FORCE
LAST_ACTIVE_TIME	DATE	Time at which the query plan was last active
BIND_DATA	RAW(2000)	Bind data

V$SQL_PLAN

V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

Column	Datatype	Description
ADDRESS	RAW(4 \| 8)	Address of the handle to the parent for this cursor
HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache. The two columns ADDRESS and HASH_VALUE can be used to join with V$SQLAREA to add the cursor-specific information.
SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
PLAN_HASH_VALUE	NUMBER	Numerical representation of the SQL plan for the cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
CHILD_NUMBER	NUMBER	Number of the child cursor that uses this execution plan. The columns ADDRESS, HASH_VALUE, and CHILD_NUMBER can be used to join withV$SQL to add the child cursor-specific information.
OPERATION	VARCHAR2(30)	Name of the internal operation performed in this step (for example, TABLE ACCESS)
OPTIONS	VARCHAR2(30)	A variation on the operation described in the OPERATION column (for example, FULL)
OBJECT_NODE	VARCHAR2(40)	Name of the database link used to reference the object (a table name or view name). For local queries that use parallel execution, this column describes the order in which output from operations is consumed.
OBJECT#	NUMBER	Object number of the table or the index
OBJECT_OWNER	VARCHAR2(31)	Name of the user who owns the schema containing the table or index
OBJECT_NAME	VARCHAR2(31)	Name of the table or index
OBJECT_ALIAS	VARCHAR2(65)	Alias for the object
OBJECT_TYPE	VARCHAR2(20)	Type of the object
OPTIMIZER	VARCHAR2(20)	Current mode of the optimizer for the first row in the plan (statement line), for example, CHOOSE. When the operation is a database access (for example, TABLE ACCESS), this column indicates whether or not the object is analyzed.
ID	NUMBER	A number assigned to each step in the execution plan
PARENT_ID	NUMBER	ID of the next execution step that operates on the output of the current step
DEPTH	NUMBER	Depth (or level) of the operation in the tree. It is not necessary to issue a CONNECT BY statement to get the level information, which is generally used to indent the rows from the PLAN_TABLE table. The root operation (statement) is level 0.
POSITION	NUMBER	Order of processing for all operations that have the same PARENT_ID
SEARCH_COLUMNS	NUMBER	Number of index columns with start and stop keys (that is, the number of columns with matching predicates)
COST	NUMBER	Cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
CARDINALITY	NUMBER	Estimate, by the cost-based optimizer, of the number of rows produced by the operation
BYTES	NUMBER	Estimate, by the cost-based optimizer, of the number of bytes produced by the operation
OTHER_TAG	VARCHAR2(35)	Describes the contents of the OTHER column. See EXPLAIN PLAN for values.
PARTITION_START	VARCHAR2(5)	Start partition of a range of accessed partitions
PARTITION_STOP	VARCHAR2(5)	Stop partition of a range of accessed partitions
PARTITION_ID	NUMBER	Step that computes the pair of values of the PARTITION_START and PARTITION_STOP columns
OTHER	VARCHAR2(4000)	Other information specific to the execution step that users may find useful. See EXPLAIN PLAN for values.
DISTRIBUTION	VARCHAR2(20)	Stores the method used to distribute rows from producer query servers to consumer query servers
CPU_COST	NUMBER	CPU cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
IO_COST	NUMBER	I/O cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
TEMP_SPACE	NUMBER	Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
ACCESS_PREDICATES	VARCHAR2(4000)	Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.
FILTER_PREDICATES	VARCHAR2(4000)	Predicates used to filter rows before producing them
PROJECTION	VARCHAR2(4000)	Expressions produced by the operation
TIME	NUMBER	Elapsed time (in seconds) of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
QBLOCK_NAME	VARCHAR2(31)	Name of the query block
REMARKS	VARCHAR2(4000)	Remarks
OTHER_XML	CLOB	Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML since multiple pieces of information can be stored there. This includes: Name of the schema against which the query was parsed Release number of the Oracle Database that produced the explain plan Hash value associated with the execution plan Name (if any) of the outline or the SQL profile used to build the execution plan Indication of whether or not dynamic sampling was used to produce the plan The outline data, a set of optimizer hints that can be used to regenerate the same plan

V$SGASTAT

V$SGASTAT displays detailed information on the system global area (SGA).

Column	Datatype	Description
POOL	VARCHAR2(12)	Designates the pool in which the memory in NAME resides: shared pool - Memory is allocated from the shared pool large pool - Memory is allocated from the large pool java pool - Memory is allocated from the Java pool streams pool - Memory is allocated from the Streams pool
NAME	VARCHAR2(26)	SGA component name
BYTES	NUMBER	Memory size in bytes

X$KSMSP

X$KSMSP:kernal Storage Memory Sga HeaP

每一行代表shared pool 中的一个chunk

KSMCHSIZ #表示每个chunk的大小 16---3981312

KSMCHCLS #表示类型，主要有4类:

free #free chunks 表示可以自由分配

recr #recreatable chunks 可以被重新创建的chunks

freeable #可以被释放的chunks

perm #永久包含的对象

V$SYSSTAT

This view lists system statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.

Column	Datatype	Description
STATISTIC#	NUMBER	Statistic number Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications.
NAME	VARCHAR2(64)	Statistic name
CLASS	NUMBER	A number representing one or more statistics class. The following class numbers are additive: 1 - User 2 - Redo 4 - Enqueue 8 - Cache 16 - OS 32 - Real Application Clusters 64 - SQL 128 - Debug
VALUE	NUMBER	Statistic value
STAT_ID	NUMBER	Identifier of the statistic

V$LIBRARYCACHE

This view contains statistics about library cache performance and activity.

Column	Datatype	Description
`NAMESPACE`	`VARCHAR2(15)`	`Library cache namespace`
`GETS`	`NUMBER`	`Number of times a lock was requested for objects of this namespace`
`GETHITS`	`NUMBER`	`Number of times an object's handle was found in memory`
`GETHITRATIO`	`NUMBER`	`Ratio of GETHITS to GETS`
`PINS`	`NUMBER`	`Number of times a PIN was requested for objects of this namespace`
`PINHITS`	`NUMBER`	`Number of times all of the metadata pieces of the library object were found in memory`
`PINHITRATIO`	`NUMBER`	`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`	`Number of GET requests lock instance locks`
`DLM_PIN_REQUESTS`	`NUMBER`	`Number of PIN requests lock instance locks`
`DLM_PIN_RELEASES`	`NUMBER`	`Number of release requests PIN instance locks`
`DLM_INVALIDATION_REQUESTS`	`NUMBER`	`Number of GET requests for invalidation instance locks`
`DLM_INVALIDATIONS`	`NUMBER`	`Number of invalidation pings received from other instances`

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(11)`	`Parent or subordinate row cache type`
`SUBORDINATE#`	`NUMBER`	`Subordinate set number`
`PARAMETER`	`VARCHAR2(32)`	`Name of the initialization parameter that determines the number of entries in the data dictionary cache`
`COUNT`	`NUMBER`	`Total number of entries in the cache`
`USAGE`	`NUMBER`	`Number of cache entries that contain valid data`
`FIXED`	`NUMBER`	`Number of fixed entries in the cache`
`GETS`	`NUMBER`	`Total number of requests for information on the data object`
`GETMISSES`	`NUMBER`	`Number of data requests resulting in cache misses`
`SCANS`	`NUMBER`	`Number of scan requests`
`SCANMISSES`	`NUMBER`	`Number of times a scan failed to find the data in the cache`
`SCANCOMPLETES`	`NUMBER`	`For a list of subordinate entries, the number of times the list was scanned completely`
`MODIFICATIONS`	`NUMBER`	`Number of inserts, updates, and deletions`
`FLUSHES`	`NUMBER`	`Number of times flushed to disk`
`DLM_REQUESTS`	`NUMBER`	`Number of DLM requests`
`DLM_CONFLICTS`	`NUMBER`	`Number of DLM conflicts`
`DLM_RELEASES`	`NUMBER`	`Number of DLM releases`

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(64)`	`Owner of the object`
`NAME`	`VARCHAR2(1000)`	`Name of the object`
`DB_LINK`	`VARCHAR2(64)`	`Database link name, if any`
`NAMESPACE`	`VARCHAR2(28)`	`Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT`
`TYPE`	`VARCHAR2(28)`	`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` `See Also:` `"V$SQLAREA"` `to see actual execution counts`
`LOCKS`	`NUMBER`	`Number of users currently locking this object`
`PINS`	`NUMBER`	`Number of users currently pinning this object`
`KEPT`	`VARCHAR2(3)`	`(YES \| NO) Depends on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedureDBMS_SHARED_POOL.KEEP`
`CHILD_LATCH`	`NUMBER`	`Child latch number that is protecting the object`
`INVALIDATIONS`	`NUMBER`	`Total number of times objects in the namespace were marked invalid because a dependent object was modified`

V$SGA_DYNAMIC_COMPONENTS

V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup. All sizes are expressed in bytes.

Column	Datatype	Description
COMPONENT	VARCHAR2(64)	Component name
CURRENT_SIZE	NUMBER	Current size of the component
MIN_SIZE	NUMBER	Minimum size of the component since instance startup
MAX_SIZE	NUMBER	Maximum size of the component since instance startup
USER_SPECIFIED_SIZE	NUMBER	Value of the user parameter for the component
OPER_COUNT	NUMBER	Number of operations since instance startup
LAST_OPER_TYPE	VARCHAR2(13)	Last completed operation for the component: STATIC INITIALIZING DISABLED GROW SHRINK SHRINK_CANCEL
LAST_OPER_MODE	VARCHAR2(9)	Mode of the last completed operation: MANUAL DEFERRED IMMEDIATE
LAST_OPER_TIME	DATE	Start time of the last completed operation
GRANULE_SIZE	NUMBER	Granularity of the grow or the shrink operation

V$SHARED_POOL_ADVICE

V$SHARED_POOL_ADVICE displays information about estimated parse time in the shared pool for different pool sizes. The sizes range from 10% of the current shared pool size or the amount of pinned library cache memory (whichever is higher) to 200% of the current shared pool size, in equal intervals. The value of the interval depends on the current size of the shared pool.

Column	Datatype	Description
`SHARED_POOL_SIZE_FOR_ESTIMATE`	`NUMBER`	`Shared pool size for the estimate (in megabytes)`
`SHARED_POOL_SIZE_FACTOR`	`NUMBER`	`Size factor with respect to the current shared pool size`
`ESTD_LC_SIZE`	`NUMBER`	`Estimated memory in use by the library cache (in megabytes)`
`ESTD_LC_MEMORY_OBJECTS`	`NUMBER`	`Estimated number of library cache memory objects in the shared pool of the specified size`
`ESTD_LC_TIME_SAVED`	`NUMBER`	`Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory.`
`ESTD_LC_TIME_SAVED_FACTOR`	`NUMBER`	`Estimated parse time saved factor with respect to the current shared pool size`
`ESTD_LC_LOAD_TIME`	`NUMBER`	`Estimated elapsed time (in seconds) for parsing in a shared pool of the specified size`
`ESTD_LC_LOAD_TIME_FACTOR`	`NUMBER`	`Estimated load time factor with respect to the current shared pool size`
`ESTD_LC_MEMORY_OBJECT_HITS`	`NUMBER`	`Estimated number of times a library cache memory object was found in a shared pool of the specified size`

V$DB_CACHE_ADVICE

V$DB_CACHE_ADVICE contains rows that predict the number of physical reads for the cache size corresponding to each row. The rows also compute a "physical read factor," which is the ratio of the number of estimated reads to the number of reads actually performed by the real buffer cache during the measurement interval.

See Also:

"DB_CACHE_ADVICE"

Column	Datatype	Description
`ID`	`NUMBER`	`Buffer pool identifier (ranges from 1 to 8)`
`NAME`	`VARCHAR2(20)`	`Buffer pool name`
`BLOCK_SIZE`	`NUMBER`	`Block size in bytes for buffers in this pool. Possible values: the standard block size, the power of 2 non-standard block sizes, 2048,4096, 8192, 16384, 32768.`
`ADVICE_STATUS`	`VARCHAR2(3)`	`Status of the advisory. ON indicates it is currently running; OFF indicates it is disabled (in this case the estimates are historical and calculated when last enabled).`
`SIZE_FOR_ESTIMATE`	`NUMBER`	`Cache size for prediction (in megabytes)`
`SIZE_FACTOR`	`NUMBER`	`Size factor with respect to the current cache size`
`BUFFERS_FOR_ESTIMATE`	`NUMBER`	`Cache size for prediction (in terms of buffers)`
`ESTD_PHYSICAL_READ_FACTOR`	`NUMBER`	`Physical read factor for this cache size, which is the ratio of the number of estimated physical reads to the number of reads in the real cache. If there are no physical reads in the real cache, the value of this column is null.`
`ESTD_PHYSICAL_READS`	`NUMBER`	`Estimated number of physical reads for this cache size`
`ESTD_PHYSICAL_READ_TIME`	`NUMBER`	`Estimated disk read time`
`ESTD_PCT_OF_DB_TIME_FOR_READS`	`NUMBER`	`Estimated disk time as a percentage of the total time`
`ESTD_CLUSTER_READS`	`NUMBER`	`Estimated total number of blocks foreground processes read from the global cache (Real Application Clusters only)`
`ESTD_CLUSTER_READ_TIME`	`NUMBER`	`Estimated total amount of time, in seconds, foreground processes read from global cache (Real Application Clusters only)`

DBA_DATA_FILES

DBA_DATA_FILES describes database files.

Column	Datatype	NULL	Description
FILE_NAME	VARCHAR2(513)		Name of the database file
FILE_ID	NUMBER	NOT NULL	File identifier number of the database file
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace to which the file belongs
BYTES	NUMBER		Size of the file in bytes
BLOCKS	NUMBER	NOT NULL	Size of the file in Oracle blocks
STATUS	VARCHAR2(9)		File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped)
RELATIVE_FNO	NUMBER		Relative file number
AUTOEXTENSIBLE	VARCHAR2(3)		Autoextensible indicator
MAXBYTES	NUMBER		Maximum file size in bytes
MAXBLOCKS	NUMBER		Maximum file size in blocks
INCREMENT_BY	NUMBER		Number of tablespace blocks used as autoextension increment. Block size is contained in theBLOCK_SIZE column of the DBA_TABLESPACES view.
USER_BYTES	NUMBER		The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata.
USER_BLOCKS	NUMBER		Number of blocks which can be used by the data
ONLINE_STATUS	VARCHAR2(7)		Online status of the file: SYSOFF SYSTEM OFFLINE ONLINE RECOVER

X$BH

Fixed Table Buffer Cache Diagram

Column Type Description

ADDR Hex address of the Buffer Header.

INDX Buffer Header number

HLADDR Hash Chain Latch Address

LRU_FLAG LRU flag

KCBBHLDF 0x01 LRU Dump Flag used in debug print routine

KCBBHLMT 0x02 moved to tail of lru (for extended stats)

KCBBHLAL 0x04 on auxiliary list

KCBBHLHB 0x08 hot buffer - not in cold portion of lru

FLAG NUMBER

KCBBHFBD 0x00001 buffer dirty

KCBBHFAM 0x00002 7.3 about to modify; try not to start io

KCBBHFAM 0x00002 8.0 about to modify; try not to start io

KCBBHNAC 0x00002 8.1 notify dbwr after change

KCBBHFMS 0x00004 modification started, no new writes

KCBBHFBL 0x00008 block logged

KCBBHFTD 0x00010 temporary data - no redo for changes

KCBBHFBW 0x00020 being written; can't modify

KCBBHFWW 0x00040 waiting for write to finish

KCBBHFCK 0x00080 7.3 checkpoint asap

0x00080 8.0 not used

KCBBHFMW 0x00080 8.1 multiple waiters when gc lock acquired

KCBBHFRR 0x00100 recovery reading, do not reuse, being read

KCBBHFUL 0x00200 unlink from lock element - make non-current

KCBBHFDG 0x00400 write block & stop using for lock down grade

KCBBHFCW 0x00800 write block for cross instance call

KCBBHFCR 0x01000 reading from disk into KCBBHCR buffer

KCBBHFGC 0x02000 has been gotten in current mode

KCBBHFST 0x04000 stale - unused CR buf made from current

0x08000 7.3 Not used.

KCBBHFDP 0x08000 8.0 deferred ping

KCBBHFDP 0x08000 8.1 deferred ping

KCBBHFDA 0x10000 Direct Access to buffer contents

KCBBHFHD 0x20000 Hash chain Dump used in debug print routine

KCBBHFIR 0x40000 Ignore Redo for instance recovery

KCBBHFSQ 0x80000 sequential scan only flag

KCBBHFNW 0x100000 7.3 Set to indicate a buffer that is NEW

0x100000 8.0 Not used

KCBBHFBP 0x100000 8.1 Indicates that buffer was prefetched

KCBBHFRW 0x200000 7.3 re-write if being written (sort)

0x200000 8.0 Not used

KCBBHFFW 0x200000 8.1 Buffer has been written once

KCBBHFFB 0x400000 buffer is "logically" flushed

KCBBHFRS 0x800000 ReSilvered already - do not redirty

KCBBHFKW 0x1000000 7.3 ckpt writing flag to avoid rescan */

0x1000000 8.0 Not used

KCBBHDRC 0x1000000 8.1 buffer is nocache

0x2000000 7.3 Not used

KCBBHFRG 0x2000000 8.0 Redo Generated since block read

KCBBHFRG 0x2000000 8.1 Redo Generated since block read

KCBBHFWS 0x10000000 8.0 Skipped write for checkpoint.

KCBBHFDB 0x20000000 8.1 buffer is directly from a foreign DB

KCBBHFAW 0x40000000 8.0 Flush after writing

KCBBHFAW 0x40000000 8.1 Flush after writing

TS# NUMBER 8.X Tablespace number

DBARFIL NUMBER 8.X Relative file number of block

DBAFIL NUMBER 7.3 File number of block

DBABLK NUMBER Block number of block

CLASS NUMBER

STATE NUMBER

KCBBHFREE 0 buffer free

KCBBHEXLCUR 1 buffer current (and if DFS locked X)

KCBBHSHRCUR 2 buffer current (and if DFS locked S)

KCBBHCR 3 buffer consistant read

KCBBHREADING 4 Being read

KCBBHMRECOVERY 5 media recovery (current & special)

KCBBHIRECOVERY 6 Instance recovery (somewhat special)

MODE_HELD NUMBER Mode buffer held in (MODE pre 7.3)

0=KCBMNULL, KCBMSHARE, KCBMEXCL

CHANGES NUMBER

CSTATE NUMBER

X_TO_NULL NUMBER Count of PINGS out (OPS)

DIRTY_QUEUE NUMBER You wont normally see buffers on the LRUW

LE_ADDR RAW(4) Lock Element address (OPS)

SET_DS RAW(4) Buffer cache set this buffer is under

OBJ NUMBER Data object number

TCH NUMBER 8.1 Touch Count

TIM NUMBER 8.1 Touch Time

BA RAW(4)

CR_SCN_BAS NUMBER Consistent Read SCN base

CR_SCN_WRP NUMBER Consistent Read SCN wrap

CR_XID_USN NUMBER CR XID Undo segment no

CR_XID_SLT NUMBER CR XID slot

CR_XID_SQN NUMBER CR XID Sequence

CR_UBA_FIL NUMBER CR UBA file

CR_UBA_BLK NUMBER CR UBA Block

CR_UBA_SEQ NUMBER CR UBA sequence

CR_UBA_REC NUMBER CR UBA record

CR_SFL NUMBER

LRBA_SEQ NUMBER } Lowest RBA needed to recover block in cache

LRBA_BNO NUMBER }

LRBA_BOF NUMBER }

HRBA_SEQ NUMBER } Redo RBA to be flushed BEFORE this block

HRBA_BNO NUMBER } can be written out

HRBA_BOF NUMBER }

RRBA_SEQ NUMBER } Block recovery RBA

RRBA_BNO NUMBER }

RRBA_BOF NUMBER }

NXT_HASH NUMBER Next buffer on this hash chain

PRV_HASH NUMBER Previous buffer on this hash chain

NXT_LRU NUMBER Next buffer on the LRU

PRV_LRU NUMBER Previous buffer on the LRU

US_NXT RAW(4)

US_PRV RAW(4)

WA_NXT RAW(4)

WA_PRV RAW(4)

ACC RAW(4)

MOD RAW(4)

ALL_OBJECTS

ALL_OBJECTS describes all objects accessible to the current user.

Related Views

DBA_OBJECTS describes all objects in the database.

USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object
OBJECT_NAME	VARCHAR2(30)	NOT NULL	Name of the object
SUBOBJECT_NAME	VARCHAR2(30)		Name of the subobject (for example, partition)
OBJECT_ID	NUMBER	NOT NULL	Dictionary object number of the object
DATA_OBJECT_ID	NUMBER		Dictionary object number of the segment that contains the object
			Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that the Oracle Database assigns to row objects in object tables in the system.
OBJECT_TYPE	VARCHAR2(19)		Type of the object (such as TABLE, INDEX)
CREATED	DATE	NOT NULL	Timestamp for the creation of the object
LAST_DDL_TIME	DATE	NOT NULL	Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes)
TIMESTAMP	VARCHAR2(20)		Timestamp for the specification of the object (character data)
STATUS	VARCHAR2(7)		Status of the object (VALID, INVALID, or N/A)
TEMPORARY	VARCHAR2(1)		Whether the object is temporary (the current session can see only data that it placed in this object itself)
GENERATED	VARCHAR2(1)		Indicates whether the name of this object was system generated (Y) or not (N)
SECONDARY	VARCHAR2(1)		Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y \| N)

V$FILESTAT

This view displays the number of physical reads and writes done and the total number of single-block and multiblock I/Os done at file level. As of Oracle Database 10g Release 2 (10.2), this view also includes reads done by RMAN processes for backup operations.

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`
`SINGLEBLKRDS`	`NUMBER`	`Number of single block reads`
`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`
`SINGLEBLKRDTIM`	`NUMBER`	`Cumulative single block read time (in hundredths of a second)`
`AVGIOTIM`	`NUMBER`	`Average time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is true; 0 if false`
`LSTIOTIM`	`NUMBER`	`Time (in hundredths of a second) spent doing the last I/O, if the TIMED_STATISTICS parameter is true; 0 if false`
`MINIOTIM`	`NUMBER`	`Minimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICS parameter is true; 0 if false`
`MAXIORTM`	`NUMBER`	`Maximum time (in hundredths of a second) spent doing a single read, if the TIMED_STATISTICS parameter is true; 0 if false`
`MAXIOWTM`	`NUMBER`	`Maximum time (in hundredths of a second) spent doing a single write, if the TIMED_STATISTICS parameter is true; 0 if false`

V$SGAINFO

V$SGAINFO displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.

Column	Datatype	Description
`NAME`	`VARCHAR2(32)`	`Name of the SGA statistic`
`BYTES`	`NUMBER`	`Size of the statistic (in bytes)`
`RESIZEABLE`	`VARCHAR2(3)`	`Indicates whether the statistic is resizeable (YES) or not (NO)`

V$VERSION

V$VERSION displays version numbers of core library components in the Oracle Database. There is one row for each component.

Column	Datatype	Description
`BANNER`	`VARCHAR2(64)`	`Component name and version number`

V$LOG

V$LOG displays log file information from the control file.

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(3)	Archive status (YES or NO)
STATUS	VARCHAR2(16)	Log status: UNUSED - 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 - Current redo log. This implies that the redo log is active. The redo log could be open or closed. ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED. CLEARING_CURRENT - 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 - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
FIRST_CHANGE#	NUMBER	Lowest system change number (SCN) in the log
FIRST_TIME	DATE	Time of the first SCN in the log

V$LOGFILE

This view contains information about redo log files.

Column	Datatype	Description
GROUP#	NUMBER	Redo log group identifier number
STATUS	VARCHAR2(7)	Status of the log member: INVALID - File is inaccessible STALE - File's contents are incomplete DELETED - File is no longer used null - File is in use
TYPE	VARCHAR2(7)	Type of the logfile: ONLINE STANDBY
MEMBER	VARCHAR2(513)	Redo log member name
IS_RECOVERY_DEST_FILE	VARCHAR2(3)	Indicates whether the file was created in the flash recovery area (YES) or not (NO)

V$LOG_HISTORY

V$LOG_HISTORY displays log history information from the control file.

Column	Datatype	Description
`RECID`	`NUMBER`	`Control file record ID`
`STAMP`	`NUMBER`	`Control file record stamp`
`THREAD#`	`NUMBER`	`Thread number of the archived log`
`SEQUENCE#`	`NUMBER`	`Sequence number of the archived log`
`FIRST_CHANGE#`	`NUMBER`	`Lowest system change number (SCN) in the log`
`FIRST_TIME`	`DATE`	`Time of the first entry (lowest SCN) in the log`
`NEXT_CHANGE#`	`NUMBER`	`Highest SCN in the log`
`RESETLOGS_CHANGE#`	`NUMBER`	`Resetlogs change number of the database when the log was written`
`RESETLOGS_TIME`	`DATE`	`Resetlogs time of the database when the log was written`

V$INSTANCE_RECOVERY

V$INSTANCE_RECOVERY monitors the mechanisms available to users to limit recovery I/O. Those mechanisms are:

Set the LOG_CHECKPOINT_TIMEOUT initialization parameter

Set the LOG_CHECKPOINT_INTERVAL initialization parameter

Set the FAST_START_MTTR_TARGET initialization parameter

Set the size of the smallest redo log

Column	Datatype	Description
RECOVERY_ESTIMATED_IOS	NUMBER	Number of dirty buffers in the buffer cache. In the Standard Edition, this column is always null.
ACTUAL_REDO_BLKS	NUMBER	Current actual number of redo blocks required for recovery
TARGET_REDO_BLKS	NUMBER	Current target number of redo blocks that must be processed for recovery. This value is the minimum value of the following 3 columns, and identifies which of the 3 user-defined limits determines checkpointing.
LOG_FILE_SIZE_REDO_BLKS	NUMBER	Maximum number of redo blocks required to guarantee that a log switch does not occur before the checkpoint completes.
LOG_CHKPT_TIMEOUT_REDO_BLKS	NUMBER	Number of redo blocks that need to be processed during recovery to satisfy the LOG_CHECKPOINT_TIMEOUT parameter. The value displayed is not meaningful unless that parameter has been set.
LOG_CHKPT_INTERVAL_REDO_BLKS	NUMBER	Number of redo blocks that need to be processed during recovery to satisfy the LOG_CHECKPOINT_INTERVAL parameter. The value displayed is not meaningful unless that parameter has been set.
FAST_START_IO_TARGET_REDO_BLKS	NUMBER	This column is obsolete and maintained for backward compatibility. The value of this column is always null.
TARGET_MTTR	NUMBER	Effective MTTR (mean time to recover) target value in seconds. The TARGET_MTTR value is calculated based on the value of theFAST_START_MTTR_TARGET parameter (the TARGET_MTTR value is used internally), and is usually an approximation of the parameter's value. However, if the FAST_START_MTTR_TARGET parameter value is very small (for example, one second), or very large (for example, 3600seconds), the calculation will produce a target value dictated by system limitations. In such cases, the TARGET_MTTR value will be the shortest calculated time, or the longest calculated time that recovery is expected to take. If FAST_START_MTTR_TARGET is not specified, the value of this field is the current estimated MTTR.
ESTIMATED_MTTR	NUMBER	Current estimated mean time to recover (MTTR) based on the number of dirty buffers and log blocks (0 if FAST_START_MTTR_TARGET is not specified). Basically, this value tells you how long you could expect recovery to take based on the work your system is doing right now.
CKPT_BLOCK_WRITES	NUMBER	Number of blocks written by checkpoint writes
OPTIMAL_LOGFILE_SIZE	NUMBER	Redo log file size (in megabytes) that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. It is recommended that the user configure all online redo logs to be at least this value.
ESTD_CLUSTER_AVAILABLE_TIME	NUMBER	Estimated time (in seconds) that the cluster would become partially available should this instance fail. This column is only meaningful in a Real Application Clusters (RAC) environment. In a non-RAC environment, the value of this column is null.
WRITES_MTTR	NUMBER	Number of writes driven by the FAST_START_MTTR_TARGET initialization parameter
WRITES_LOGFILE_SIZE	NUMBER	Number of writes driven by the smallest redo log file size
WRITES_LOG_CHECKPOINT_SETTINGS	NUMBER	Number of writes driven by the LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT initialization parameter
WRITES_OTHER_SETTINGS	NUMBER	Number of writes driven by other reasons (such as the deprecated FAST_START_IO_TARGET initialization parameter)
WRITES_AUTOTUNE	NUMBER	Number of writes due to auto-tune checkpointing
WRITES_FULL_THREAD_CKPT	NUMBER	Number of writes due to full thread checkpoints

V$ARCHIVED_LOG

V$ARCHIVED_LOG displays archived log information from the control file, 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 and whenever a copy of a log is made with the RMAN COPY command.

Column	Datatype	Description
`RECID`	`NUMBER`	`Archived log record ID`
`STAMP`	`NUMBER`	`Archived log record stamp`
`NAME`	`VARCHAR2(513)`	`Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;).`
`DEST_ID`	`NUMBER`	`Original destination from which the archive log was generated. The value is 0 if the destination identifier is not available.`
`THREAD#`	`NUMBER`	`Redo thread number`
`SEQUENCE#`	`NUMBER`	`Redo log sequence number`
`RESETLOGS_CHANGE#`	`NUMBER`	`Resetlogs change number of the database when the log was written`
`RESETLOGS_TIME`	`DATE`	`Resetlogs time of the database when the log was written`
`RESETLOGS_ID`	`NUMBER`	`Resetlogs identifier associated with the archived redo log`
`FIRST_CHANGE#`	`NUMBER`	`First change number 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. This is the logical block size of the archived log, which is the same as the logical block size of the online log from which the archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user.`
`CREATOR`	`VARCHAR2(7)`	`Creator of the archivelog:` `ARCH - Archiver process` `FGRD - Foreground process` `RMAN - Recovery Manager` `SRMN - RMAN at standby` `LGWR - Logwriter process`
`REGISTRAR`	`VARCHAR2(7)`	`Registrar of the entry:` `RFS - Remote File Server process` `ARCH - Archiver process` `FGRD - Foreground process` `RMAN - Recovery manager` `SRMN - RMAN at standby` `LGWR - Logwriter process`
`STANDBY_DEST`	`VARCHAR2(3)`	`Indicates whether the entry is an archivelog destination (YES) or not (NO)`
`ARCHIVED`	`VARCHAR2(3)`	`Indicates whether the online redo log was archived (YES) or whether RMAN only inspected the log and created a record for future application of redo logs during recovery (NO).` `See Also:` `Oracle Database Backup and Recovery Basics.`
`APPLIED`	`VARCHAR2(3)`	`Indicates whether the archivelog has been applied to its corresponding standby database (YES) or not (NO). The value is always NO for local destinations.` `This column is meaningful at the physical standby site for the ARCHIVED_LOG entries with REGISTRAR='RFS' (which means this log is shipped from the primary to the standby database). If REGISTRAR='RFS' and APPLIED is NO, then the log has arrived at the standby but has not yet been applied. If REGISTRAR='RFS' and APPLIED is YES, the log has arrived and been applied at the standby database.` `You can use this field to identify archivelogs that can be backed up and removed from disk.`
`DELETED`	`VARCHAR2(3)`	`Indicates whether an RMAN DELETE command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog (YES) or not (NO)`
`STATUS`	`VARCHAR2(1)`	`Status of the archived log:` `A - Available` `D - Deleted` `U - Unavailable` `X - Expired`
`COMPLETION_TIME`	`DATE`	`Time when the archiving completed`
`DICTIONARY_BEGIN`	`VARCHAR2(3)`	`Indicates whether the log contains the start of a LogMiner dictionary (YES) or not (NO)`
`DICTIONARY_END`	`VARCHAR2(3)`	`Indicates whether the log contains the end of a LogMiner dictionary (YES) or not (NO)`
`END_OF_REDO`	`VARCHAR2(3)`	`Indicates whether the archived redo log contains the end of all redo information from the primary database (YES) or not (NO)`
`BACKUP_COUNT`	`NUMBER`	`Indicates the number of times this file has been backed up. Values range from 0-15. If the file has been backed up more than 15 times, the value remains 15.`
`ARCHIVAL_THREAD#`	`NUMBER`	`Redo thread number of the instance that performed the archival operation. This column differs from the THREAD# column only when a closed thread is archived by another instance.`
`ACTIVATION#`	`NUMBER`	`Number assigned to the database instantiation`
`IS_RECOVERY_DEST_FILE`	`VARCHAR2(3)`	`Indicates whether the file was created in the flash recovery area (YES) or not (NO)`
`COMPRESSED`	`VARCHAR2(3)`	`This column is reserved for internal use only`
`FAL`	`VARCHAR2(3)`	`Indicates whether the archive log was generated as the result of a FAL request (YES) or not (NO)`
`END_OF_REDO_TYPE`	`VARCHAR2(10)`	`Possible values are as follows:` `SWITCHOVER - Shows archived redo log files that are produced at the end of a switchover` `TERMINAL - Shows archived redo log files produced after a failover` `RESETLOGS - Shows online redo log files archived on the primary database after an ALTER DATABASE OPEN RESETLOGS statement is issued` `ACTIVATION - Shows any log files archived on a physical standby database after an ALTER DATABASE ACTIVATE STANDBY DATABASEstatement is issued` `"empty string" - Any empty string implies that that the log is just a normal archival and was not archived due to any of the other events`

V$RECOVER_FILE

This view displays the status of files needing media recovery.

Column	Datatype	Description
`FILE#`	`NUMBER`	`File identifier number`
`ONLINE`	`VARCHAR2(7)`	`This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value inONLINE_STATUS.`
`ONLINE_STATUS`	`VARCHAR2(7)`	`Online status (ONLINE, OFFLINE)`
`ERROR`	`VARCHAR2(18)`	`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$LOGHIST

This view contains log history information from the control file. This view is retained for historical compatibility. Oracle recommends that you use V$LOG_HISTORY instead.

See Also:

"V$LOG_HISTORY"

Column	Datatype	Description
`THREAD#`	`NUMBER`	`Log thread number`
`SEQUENCE#`	`NUMBER`	`Log sequence number`
`FIRST_CHANGE#`	`NUMBER`	`Lowest SCN in the log`
`FIRST_TIME`	`DATE`	`Time of first SCN in the log`
`SWITCH_CHANGE#`	`NUMBER`	`SCN at which the log switch occurred; one more than highest SCN in the log`

V$MYSTAT

This view contains statistics on the current session.

Column	Datatype	Description
`SID`	`NUMBER`	`ID of the current session`
`STATISTIC#`	`NUMBER`	`Number of the statistic`
`VALUE`	`NUMBER`	`Value of the statistic`

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 \| 8)`	`Address of process state object`
`PID`	`NUMBER`	`Oracle process identifier`
`SPID`	`VARCHAR2(12)`	`Operating system process identifier`
`USERNAME`	`VARCHAR2(15)`	`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(30)`	`Operating system terminal identifier`
`PROGRAM`	`VARCHAR2(48)`	`Program in progress`
`TRACEID`	`VARCHAR2(255)`	`Trace file identifier`
`BACKGROUND`	`VARCHAR2(1)`	`1 for a background process; NULL for a normal process`
`LATCHWAIT`	`VARCHAR2(8)`	`Address of latch the process is waiting for; NULL if none`
`LATCHSPIN`	`VARCHAR2(8)`	`Address of the latch the process is spinning on; NULL if none`
`PGA_USED_MEM`	`NUMBER`	`PGA memory currently used by the process`
`PGA_ALLOC_MEM`	`NUMBER`	`PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)`
`PGA_FREEABLE_MEM`	`NUMBER`	`Allocated PGA memory which can be freed`
`PGA_MAX_MEM`	`NUMBER`	`Maximum PGA memory ever allocated by the process`

V$DATAFILE_HEADER

This view displays datafile information from the datafile headers.

Column	Datatype	Description
`FILE#`	`NUMBER`	`Datafile number (from control file)`
`STATUS`	`VARCHAR2(7)`	`ONLINE \| OFFLINE (from control file)`
`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(513)`	`Datafile name`
`SPACE_HEADER`	`VARCHAR2(40)`	`The amount of space currently being used and the amount that is free, as identified in the space header`
`LAST_DEALLOC_SCN`	`VARCHAR2(16)`	`Last deallocated SCN`

V$DATAFILE

This view contains datafile information from the control file.

See Also:

"V$DATAFILE_HEADER", which displays information from datafile headers

Column	Datatype	Description
`FILE#`	`NUMBER`	`File identification number`
`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`
`STATUS`	`VARCHAR2(7)`	`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:` `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`
`CHECKPOINT_CHANGE#`	`NUMBER`	`SCN at last checkpoint`
`CHECKPOINT_TIME`	`DATE`	`Timestamp of the checkpoint#`
`UNRECOVERABLE_CHANGE#`	`NUMBER`	`Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated.`
`UNRECOVERABLE_TIME`	`DATE`	`Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.`
`LAST_CHANGE#`	`NUMBER`	`Last change number made to this datafile (null if the datafile is being changed)`
`LAST_TIME`	`DATE`	`Timestamp of the last change`
`OFFLINE_CHANGE#`	`NUMBER`	`Offline change number of the last offline range. This column is updated only when the datafile is brought online.`
`ONLINE_CHANGE#`	`NUMBER`	`Online change number of the last offline range`
`ONLINE_TIME`	`DATE`	`Online timestamp of the last offline range`
`BYTES`	`NUMBER`	`Current datafile size (in bytes); 0 if inaccessible`
`BLOCKS`	`NUMBER`	`Current datafile size (in blocks); 0 if inaccessible`
`CREATE_BYTES`	`NUMBER`	`Size when created (in bytes)`
`BLOCK_SIZE`	`NUMBER`	`Block size of the datafile`
`NAME`	`VARCHAR2(513)`	`Name of the datafile`
`PLUGGED_IN`	`NUMBER`	`Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read/write, 0 if not.`
`BLOCK1_OFFSET`	`NUMBER`	`Offset from the beginning of the file to where the Oracle generic information begins. The exact length of the file can be computed as follows: BYTES + BLOCK1_OFFSET.`
`AUX_NAME`	`VARCHAR2(513)`	`Auxiliary name that has been set for this file via CONFIGURE AUXNAME`
`FIRST_NONLOGGED_SCN`	`NUMBER`	`First nonlogged SCN`
`FIRST_NONLOGGED_TIME`	`DATE`	`First nonlogged time`

V$BH

This is a Real Application Clusters 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 the filename, query DBA_DATA_FILES or V$DBFILE)
BLOCK#	NUMBER	Block number
CLASS#	NUMBER	Class number
STATUS	VARCHAR2(6)	Status of the buffer: 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 and maintained for backward compatibility.
LOCK_ELEMENT_ADDR	RAW(4 \| 8)	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 reread from the cache because another instance has forced it out of this instance's cache by requesting the lock on the block in exclusive mode
FORCED_WRITES	NUMBER	Number of times GCS had to write this block to cache because this instance had used the block and another instance had requested the lock on the block in a 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 and maintained for backward compatibility.
OBJD	NUMBER	Database object number of the block that the buffer represents
TS#	NUMBER	Tablespace number of block

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(30)`	`Rollback segment name`

DBA_SEGMENTS

DBA_SEGMENTS describes the storage allocated for all segments in the database.

Related View

USER_SEGMENTS describes the storage allocated for the segments owned by the current user's objects. This view does not display the OWNER, HEADER_FILE, HEADER_BLOCK, or RELATIVE_FNO columns.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`		`Username of the segment owner`
`SEGMENT_NAME`	`VARCHAR2(81)`		`Name, if any, of the segment`
`PARTITION_NAME`	`VARCHAR2(30)`		`Object Partition Name (Set to NULL for non-partitioned objects)`
`SEGMENT_TYPE`	`VARCHAR2(18)`		`Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, CACHE, LOBSEGMENT and LOBINDEX`
`TABLESPACE_NAME`	`VARCHAR2(30)`		`Name of the tablespace containing the segment`
`HEADER_FILE`	`NUMBER`		`ID of the file containing the segment header`
`HEADER_BLOCK`	`NUMBER`		`ID of the block containing the segment header`
`BYTES`	`NUMBER`		`Size, in bytes, of the segment`
`BLOCKS`	`NUMBER`		`Size, in Oracle blocks, of the segment`
`EXTENTS`	`NUMBER`		`Number of extents allocated to the segment`
`INITIAL_EXTENT`	`NUMBER`		`Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.)`
`NEXT_EXTENT`	`NUMBER`		`Size in bytes of the next extent to be allocated to the segment`
`MIN_EXTENTS`	`NUMBER`		`Minimum number of extents allowed in the segment`
`MAX_EXTENTS`	`NUMBER`		`Maximum number of extents allowed in the segment`
`PCT_INCREASE`	`NUMBER`		`Percent by which to increase the size of the next extent to be allocated`
`FREELISTS`	`NUMBER`		`Number of process freelists allocated to this segment`
`FREELIST_GROUPS`	`NUMBER`		`Number of freelist groups allocated to this segment`
`RELATIVE_FNO`	`NUMBER`		`Relative file number of the segment header`
`BUFFER_POOL`	`VARCHAR2(7)`		`Default buffer pool for the object`

DBA_ROLLBACK_SEGS

DBA_ROLLBACK_SEGS describes rollback segments.

Column	Datatype	NULL	Description
`SEGMENT_NAME`	`VARCHAR2(30)`	`NOT NULL`	`Name of the rollback segment`
`OWNER`	`VARCHAR2(6)`		`Owner of the rollback segment`
`TABLESPACE_NAME`	`VARCHAR2(30)`	`NOT NULL`	`Name of the tablespace containing the rollback segment`
`SEGMENT_ID`	`NUMBER`	`NOT NULL`	`ID number of the rollback segment`
`FILE_ID`	`NUMBER`	`NOT NULL`	`File identifier number of the file containing the segment head`
`BLOCK_ID`	`NUMBER`	`NOT NULL`	`ID number of the block containing the segment header`
`INITIAL_EXTENT`	`NUMBER`		`Initial extent size in bytes`
`NEXT_EXTENT`	`NUMBER`		`Secondary extent size in bytes`
`MIN_EXTENTS`	`NUMBER`	`NOT NULL`	`Minimum number of extents`
`MAX_EXTENTS`	`NUMBER`	`NOT NULL`	`Maximum number of extent`
`PCT_INCREASE`	`NUMBER`		`Percent increase for extent size`
`STATUS`	`VARCHAR2(16)`		`Rollback segment status`
`INSTANCE_NUM`	`VARCHAR2(40)`		`Rollback segment owning Real Application Clusters instance number`
`RELATIVE_FNO`	`NUMBER`	`NOT NULL`	`Relative file number of the segment header`

V$PARAMETER

V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.

Column	Datatype	Description
NUM	NUMBER	Parameter number
NAME	VARCHAR2(80)	Name of the parameter
TYPE	NUMBER	Parameter type: 1 - Boolean 2 - String 3 - Integer 4 - Parameter file 5 - Reserved 6 - Big integer
VALUE	VARCHAR2(512)	Parameter value for the session (if modified within the session); otherwise, the instance-wide parameter value
DISPLAY_VALUE	VARCHAR2(512)	Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.
ISDEFAULT	VARCHAR2(9)	Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
ISSES_MODIFIABLE	VARCHAR2(5)	Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE	VARCHAR2(9)	Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect: IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately. DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions. FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
ISINSTANCE_MODIFIABLE	VARCHAR2(5)	For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If theISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
ISMODIFIED	VARCHAR2(10)	Indicates whether the parameter has been modified after instance startup: MODIFIED - Parameter has been modified with ALTER SESSION SYSTEM_MOD - Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions' values to be modified) FALSE - Parameter has not been modified after instance startup
ISADJUSTED	VARCHAR2(5)	Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
ISDEPRECATED	VARCHAR2(5)	Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)
DESCRIPTION	VARCHAR2(255)	Description of the parameter
UPDATE_COMMENT	VARCHAR2(255)	Comments associated with the most recent update
HASH	NUMBER	Hash value for the parameter name

DBA_UNDO_EXTENTS

DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database.

Column	Datatype	NULL	Description
OWNER	CHAR(3)		Owner of the undo tablespace
SEGMENT_NAME	VARCHAR2(30)	NOT NULL	Name of the undo segment
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the undo tablespace
EXTENT_ID	NUMBER		ID of the extent
FILE_ID	NUMBER	NOT NULL	File identifier number of the file containing the extent
BLOCK_ID	NUMBER		Start block number of the extent
BYTES	NUMBER		Size of the extent (in bytes)
BLOCKS	NUMBER		Size of the extent (in blocks)
RELATIVE_FNO	NUMBER		Relative number of the file containing the segment header
COMMIT_JTIME	NUMBER		Commit time of the undo in the extent expressed as Julian time. This column is deprecated, but retained for backward compatibility reasons.
COMMIT_WTIME	VARCHAR2(20)		Commit time of the undo in the extent expressed as Wallclock time. This column is deprecated, but retained for backward compatibility reasons.
STATUS	VARCHAR2(9)		Transaction Status of the undo in the extent: ACTIVE EXPIRED UNEXPIRED

DBA_TABLESPACES

DBA_TABLESPACES describes all tablespaces in the database.

Related View

USER_TABLESPACES describes the tablespaces accessible to the current user. This view does not display the PLUGGED_IN column.

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace
BLOCK_SIZE	NUMBER	NOT NULL	Tablespace block size
INITIAL_EXTENT	NUMBER		Default initial extent size
NEXT_EXTENT	NUMBER		Default incremental extent size
MIN_EXTENTS	NUMBER	NOT NULL	Default minimum number of extents
MAX_EXTENTS	NUMBER		Default maximum number of extents
PCT_INCREASE	NUMBER		Default percent increase for extent size
MIN_EXTLEN	NUMBER		Minimum extent size for this tablespace
STATUS	VARCHAR2(9)		Tablespace status: ONLINE OFFLINE READ ONLY
CONTENTS	VARCHAR2(9)		Tablespace contents: UNDO PERMANENT TEMPORARY
LOGGING	VARCHAR2(9)		Default logging attribute: LOGGING NOLOGGING
FORCE_LOGGING	VARCHAR2(3)		Indicates whether the tablespace is under force logging mode (YES) or not (NO)
EXTENT_MANAGEMENT	VARCHAR2(10)		Indicates whether the extents in the tablespace are dictionary managed (DICTIONARY) or locally managed (LOCAL)
ALLOCATION_TYPE	VARCHAR2(9)		Type of extent allocation in effect for the tablespace: SYSTEM UNIFORM USER
PLUGGED_IN	VARCHAR2(3)		Indicates whether the tablespace is plugged in (YES) or not (NO)
SEGMENT_SPACE_MANAGEMENT	VARCHAR2(6)		Indicates whether the free and used segment space in the tablespace is managed using free lists (MANUAL) or bitmaps (AUTO)
DEF_TAB_COMPRESSION	VARCHAR2(8)		Indicates whether default table compression is enabled (ENABLED) or not (DISABLED) Note: Enabling default table compression indicates that all tables in the tablespace will be created with table compression enabled unless otherwise specified.
RETENTION	VARCHAR2(11)		Undo tablespace retention: GUARANTEE - Tablespace is an undo tablespace with RETENTION specified as GUARANTEE A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail. NOGUARANTEE - Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE NOT APPLY - Tablespace is not an undo tablespace
BIGFILE	VARCHAR2(3)		Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO)

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.

See Also:

"V$LOCK"

Column	Datatype	Description
`ADDR`	`RAW(4 \| 8)`	`Address of lock state object`
`KADDR`	`RAW(4 \| 8)`	`Address of lock`
`SID`	`NUMBER`	`Identifier for session holding or acquiring the lock`
`TYPE`	`VARCHAR2(2)`	`Type of lock. Lists user and system types that can have locks.`
`ID1`	`NUMBER`	`Lock identifier #1 (depends on type)`
`ID2`	`NUMBER`	`Lock identifier #2 (depends on type)`
`LMODE`	`NUMBER`	`Lock mode in which the session holds the lock:` `0 - none` `1 - null (NULL)` `2 - row-S (SS)` `3 - row-X (SX)` `4 - share (S)` `5 - S/Row-X (SSX)` `6 - exclusive (X)`
`REQUEST`	`NUMBER`	`Lock mode in which the process requests the lock:` `0 - none` `1 - null (NULL)` `2 - row-S (SS)` `3 - row-X (SX)` `4 - share (S)` `5 - S/Row-X (SSX)` `6 - exclusive (X)`
`CTIME`	`NUMBER`	`Time since current mode was granted`
`BLOCK`	`NUMBER`	`The lock is blocking another lock`

V$LOCKED_OBJECT

This view lists all locks acquired by every transaction on the system. It shows which sessions are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.

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(30)`	`OS user name`
`PROCESS`	`VARCHAR2(12)`	`OS process ID`
`LOCKED_MODE`	`NUMBER`	`Lock mode`

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

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 7-3)`
`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).`