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