Oracle Undo Space Data Dictionary Views

The following is a list of views that are useful for viewing information about undo space in the automatic undo management mode. The dynamic performance views are useful for obtaining space information about the undo tablespace.

View	Description
V$UNDOSTAT	Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.
V$ROLLSTAT	For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace
V$TRANSACTION	Contains undo segment information
DBA_UNDO_EXTENTS	Shows the status and size of each extent in the undo tablespace.
DBA_HIST_UNDOSTAT	Contains statistical snapshots of V$UNDOSTAT information.

The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for undo space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.

Each row in the view contains statistics collected in the instance for a ten-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

Oracle data dictionary views

Oracle dynamic performance views

DBA_TABLESPACES

Oracle 11gR1

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
MAX_SIZE	NUMBER		Default maximum size of segments
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)
PREDICATE_EVALUATION	VARCHAR2(7)		Indicates whether predicates are evaluated by host (HOST) or by storage (STORAGE)
ENCRYPTED	VARCHAR2(3)		Indicates whether the tablespace is encrypted (YES) or not (NO)

Note:

1.

select tablespace_name, block_size, contents, logging, extent_management, allocation_type, segment_space_management, bigfile from DBA_TABLESPACES;

TABLESPACE _NAME	BLOCK _SIZE	CONTENTS	LOGGING	EXTENT_ MANAGEMENT	ALLOCATION _TYPE	SEGMENT _SPACE _MANAGEMENT	BIGFILE
SYSTEM	16384	PERMANENT	LOGGING	LOCAL	SYSTEM	MANUAL	NO
TEMP	16384	TEMPORARY	NOLOGGING	LOCAL	UNIFORM	MANUAL	NO
CWMLITE	16384	PERMANENT	LOGGING	LOCAL	SYSTEM	AUTO	NO
DRSYS	16384	PERMANENT	LOGGING	LOCAL	SYSTEM	AUTO	NO
ODM	16384	PERMANENT	LOGGING	LOCAL	SYSTEM	AUTO	NO
TOOLS	16384	PERMANENT	LOGGING	LOCAL	SYSTEM	AUTO	NO
USERS	16384	PERMANENT	LOGGING	LOCAL	SYSTEM	AUTO	NO

Oracle data dictionary views

DBA_TABLESPACE_GROUPS

Oracle 11gR1

DBA_TABLESPACE_GROUPS describes all tablespace groups in the database.

Column	Datatype	NULL	Description
GROUP_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace group
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace

Oracle data dictionary views

DBA_TEMP_FREE_SPACE

  Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================


Oracle 11gR1
DBA_TEMP_FREE_SPACE displays temporary space usage information at tablespace level.

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace
TABLESPACE_SIZE	NUMBER		Total size of the tablespace, in bytes
ALLOCATED_SPACE	NUMBER		Total allocated space, in bytes, including space that is currently allocated and used and space that is currently allocated and available for reuse
FREE_SPACE	NUMBER		Total free space available, in bytes, including space that is currently allocated and available for reuse and space that is currently unallocated

Oracle data dictionary views

DBA_TS_QUOTAS

Oracle 11gR1

DBA_TS_QUOTAS describes tablespace quotas for all users.

Related View

USER_TS_QUOTAS describes tablespace quotas for the current user. This view does not display the USERNAME column.

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Tablespace name
USERNAME	VARCHAR2(30)	NOT NULL	User with resource rights on the tablespace
BYTES	NUMBER		Number of bytes charged to the user
MAX_BYTES	NUMBER		User's quota in bytes, or -1 if no limit
BLOCKS	NUMBER	NOT NULL	Number of Oracle blocks charged to the user
MAX_BLOCKS	NUMBER		User's quota in Oracle blocks, or -1 if no limit
DROPPED	VARCHAR2(3)		Whether the tablespace has been dropped

Note:

1.

select * from DBA_TS_QUOTAS;

TABLESPACE_NAME	USERNAME	BYTES	MAX_BYTES	BLOCKS	MAX_BLOCKS	DROPPED
TEMP	ORAMON	0	-1	0	-1	NO
TOOLS	KVIDAL	0	104857600	0	6400	NO
TOOLS	SPOTLIGHT	9437184	-1	576	-1	NO
USERS	DKYU	0	209715200	0	12800	NO
USERS	DLEE	7340032	209715200	448	12800	NO

Oracle data dictionary views

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

Note:

1.      DBA_UNDO_EXTENTS view shows the status and size of each extent in the undo tablespace.

2.      Scripts using DBA_UNDO_EXTENTS:

-- Transaction Status of the undo in the extent can be any of the following   SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)   FROM   DBA_UNDO_EXTENTS GROUP BY STATUS; ·         ACTIVE - Undo Extent is Active, Used by a transaction. ·         EXPIRED - Undo Extent is expired (Exceeded the Undo Retention). ·         UNEXPIRED - Undo Extent will be required to honour UNDO_RETENTION.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-29 Thursday