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