Skip Headers
Oracle® Database Reference
11g Release 1 (11.1)

Part Number B28320-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


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:



CONTENTS VARCHAR2(9)   Tablespace contents:
  • UNDO



LOGGING VARCHAR2(9)   Default logging attribute:


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:


  • 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)