DBA_TABLESPACES Introduction

The view Oracle DBA_TABLESPACES describes all tablespaces in the database. so if you select it, it returns the list of the tablespaces including system and user-defined in the database. it is unlike USER_TABLESPACES, which describes the tablespaces only accessible to the current user, the following table lists the columns and description for DBA_TABLESPACES.

Please note DBA_TABLESPACES is a view not a table, so all data are stored in other real tables
Column Datatype NULL Description
TABLESPACE_NAME VARCHAR2(30) NOT NULL Tablespace name, Including system and user-defined
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 NOT NULL Default maximum number of extents
PCT_INCREASE NUMBER NOT NULL Default percent increase for extent size
MIN_EXTLEN NUMBER Minimum extent size for the tablespace
STATUS VARCHAR2(9) Tablespace status: ONLINE, OFFLINE, or READ ONLY
CONTENTS VARCHAR2(9) Tablespace contents: “PERMANENT”, or “TEMPORARY”
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) Extent management tracking: “DICTIONARY” or “LOCAL”
ALLOCATION_TYPE VARCHAR2(9) Type of extent allocation in effect for this tablespace
PLUGGED_IN VARCHAR2(3) YES – the tablespace is plugged in; NO – it is not plugged in
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) ndicates 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:
1.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.
2.NOGUARANTEE – Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE
3.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)

Example of listing All Tablespaces by the following SQL statement:
SQL>SELECT * FROM DBA_TABLESPACES;

"TABLESPACE_NAME"	"BLOCK_SIZE"	"INITIAL_EXTENT"	"NEXT_EXTENT"	"MIN_EXTENTS"	"MAX_EXTENTS"	"MAX_SIZE"	"PCT_INCREASE"	"MIN_EXTLEN"	"STATUS"	"CONTENTS"	"LOGGING"	"FORCE_LOGGING"	"EXTENT_MANAGEMENT"	"ALLOCATION_TYPE"	"PLUGGED_IN"	"SEGMENT_SPACE_MANAGEMENT"	"DEF_TAB_COMPRESSION"	"RETENTION"	"BIGFILE"	"PREDICATE_EVALUATION"	"ENCRYPTED"	"COMPRESS_FOR"
"SYSTEM"	8192	65536		1	2147483645	2147483645		65536	"ONLINE"	"PERMANENT"	"LOGGING"	"NO"	"LOCAL"	"SYSTEM"	"NO"	"MANUAL"	"DISABLED"	"NOT APPLY"	"NO"	"HOST"	"NO"	""
"SYSAUX"	8192	65536		1	2147483645	2147483645		65536	"ONLINE"	"PERMANENT"	"LOGGING"	"NO"	"LOCAL"	"SYSTEM"	"NO"	"AUTO"	"DISABLED"	"NOT APPLY"	"NO"	"HOST"	"NO"	""
"UNDOTBS1"	8192	65536		1	2147483645	2147483645		65536	"ONLINE"	"UNDO"	"LOGGING"	"NO"	"LOCAL"	"SYSTEM"	"NO"	"MANUAL"	"DISABLED"	"NOGUARANTEE"	"NO"	"HOST"	"NO"	""
"TEMP"  	8192	1048576	     1048576	1		2147483645	0	1048576	"ONLINE"	"TEMPORARY"	"NOLOGGING"	"NO"	"LOCAL"	"UNIFORM"	"NO"	"MANUAL"	"DISABLED"	"NOT APPLY"	"NO"	"HOST"	"NO"	""
"USERS"	        8192	65536		1	2147483645	2147483645		65536	"ONLINE"	"PERMANENT"	"LOGGING"	"NO"	"LOCAL"	"SYSTEM"	"NO"	"AUTO"	"DISABLED"	"NOT APPLY"	"NO"	"HOST"	"NO"	""
"EXAMPLE"	8192	65536		1	2147483645	2147483645		65536	"ONLINE"	"PERMANENT"	"NOLOGGING"	"NO"	"LOCAL"	"SYSTEM"	"YES"	"AUTO"	"DISABLED"	"NOT APPLY"	"NO"	"HOST"	"NO"	""
"AGI_INDX1"	8192	65536		1	2147483645	2147483645		65536	"ONLINE"	"PERMANENT"	"LOGGING"	"NO"	"LOCAL"	"SYSTEM"	"NO"	"AUTO"	"DISABLED"	"NOT APPLY"	"NO"	"HOST"	"NO"	""
"AGI_INDX2"	8192	65536		1	2147483645	2147483645		65536	"ONLINE"	"PERMANENT"	"LOGGING"	"NO"	"LOCAL"	"SYSTEM"	"NO"	"AUTO"	"DISABLED"	"NOT APPLY"	"NO"	"HOST"	"NO"	""

Example of listing All Tablespaces with returning explicit column and tablespace name starts with ‘AG’:

SQL>select tablespace_name,logging,status,contents,extent_management,allocation_type,initial_extent/1024 init_ext_kb,next_extent/1024 next_ext_kb,pct_increase,min_extents,max_extents/1024 max_ext_db,min_extlen from DBA_TABLESPACES where tablespace_name like ‘AG%’ order by 1;

"TABLESPACE_NAME","LOGGING","STATUS","CONTENTS","EXTENT_MANAGEMENT","ALLOCATION_TYPE","INIT_EXT_KB","NEXT_EXT_KB","PCT_INCREASE","MIN_EXTENTS","MAX_EXT_DB","MIN_EXTLEN"
"AGI_DATA1","LOGGING","ONLINE","PERMANENT","LOCAL","SYSTEM",64,,,1,2097151.9970703125,65536
"AGI_DATA2","LOGGING","ONLINE","PERMANENT","LOCAL","SYSTEM",64,,,1,2097151.9970703125,65536

Here is the definition of view TABLESPACES:

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TABLESPACES" ("TABLESPACE_NAME", "BLOCK_SIZE", "INITIAL_EXTENT", "NEXT_EXTENT", "MIN_EXTENTS", "MAX_EXTENTS", "MAX_SIZE", "PCT_INCREASE", "MIN_EXTLEN", "STATUS", "CONTENTS", "LOGGING", "FORCE_LOGGING", "EXTENT_MANAGEMENT", "ALLOCATION_TYPE", "PLUGGED_IN", "SEGMENT_SPACE_MANAGEMENT", "DEF_TAB_COMPRESSION", "RETENTION", "BIGFILE", "PREDICATE_EVALUATION", "ENCRYPTED", "COMPRESS_FOR")
AS
  SELECT ts.name,
    ts.blocksize,
    ts.blocksize                                                 * ts.dflinit,
    DECODE(bitand(ts.flags, 3), 1, to_number(NULL), ts.blocksize * ts.dflincr),
    ts.dflminext,
    DECODE(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
    DECODE(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
    DECODE(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
    ts.blocksize * ts.dflminlen,
    DECODE(ts.online$, 1, 'ONLINE', 2, 'OFFLINE', 4, 'READ ONLY', 'UNDEFINED'),
    DECODE(ts.contents$, 0, (DECODE(bitand(ts.flags, 16), 16, 'UNDO', 'PERMANENT')), 1, 'TEMPORARY'),
    DECODE(bitand(ts.dflogging, 1), 0, 'NOLOGGING', 1, 'LOGGING'),
    DECODE(bitand(ts.dflogging, 2), 0, 'NO', 2, 'YES'),
    DECODE(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
    DECODE(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM', 'UNDEFINED'),
    DECODE(ts.plugged, 0, 'NO', 'YES'),
    DECODE(bitand(ts.flags,32), 32,'AUTO', 'MANUAL'),
    DECODE(bitand(ts.flags,64), 64,'ENABLED', 'DISABLED'),
    DECODE(bitand(ts.flags,16), 16, (DECODE(bitand(ts.flags, 512), 512, 'GUARANTEE', 'NOGUARANTEE')), 'NOT APPLY'),
    DECODE(bitand(ts.flags,256), 256, 'YES', 'NO'),
    DECODE(tsattr.storattr, 1, 'STORAGE', 'HOST'),
    DECODE(bitand(ts.flags,16384), 16384, 'YES', 'NO'),
    DECODE(bitand(ts.flags,64), 0, NULL, DECODE(bitand(ts.flags,65536), 65536,'FOR ALL OPERATIONS', 'DIRECT LOAD ONLY'))
  FROM sys.ts$ ts,
    sys.x$kcfistsa tsattr
  WHERE ts.online$       != 3
  AND bitand(flags,2048) != 2048
  AND ts.ts#              = tsattr.tsid;
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."TABLESPACE_NAME"
IS
  'Tablespace name';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."BLOCK_SIZE"
IS
  'Tablespace block size';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."INITIAL_EXTENT"
IS
  'Default initial extent size';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."NEXT_EXTENT"
IS
  'Default incremental extent size';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."MIN_EXTENTS"
IS
  'Default minimum number of extents';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."MAX_SIZE"
IS
  'Default maximum size of segments';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."PCT_INCREASE"
IS
  'Default percent increase for extent size';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."MIN_EXTLEN"
IS
  'Minimum extent size for the tablespace';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."STATUS"
IS
  'Tablespace status: "ONLINE", "OFFLINE", or "READ ONLY"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."CONTENTS"
IS
  'Tablespace contents: "PERMANENT", or "TEMPORARY"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."LOGGING"
IS
  'Default logging attribute';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."FORCE_LOGGING"
IS
  'Tablespace force logging mode';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."EXTENT_MANAGEMENT"
IS
  'Extent management tracking: "DICTIONARY" or "LOCAL"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."ALLOCATION_TYPE"
IS
  'Type of extent allocation in effect for this tablespace';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."SEGMENT_SPACE_MANAGEMENT"
IS
  'Segment space management tracking: "AUTO" or "MANUAL"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."DEF_TAB_COMPRESSION"
IS
  'Default compression enabled or not: "ENABLED" or "DISABLED"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."RETENTION"
IS
  'Undo tablespace retention: "GUARANTEE", "NOGUARANTEE" or "NOT APPLY"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."BIGFILE"
IS
  'Bigfile tablespace indicator: "YES" or "NO"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."PREDICATE_EVALUATION"
IS
  'Predicates evaluated by: "HOST" or "STORAGE"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."ENCRYPTED"
IS
  'Encrypted tablespace indicator: "YES" or "NO"';
  COMMENT ON COLUMN "SYS"."DBA_TABLESPACES"."COMPRESS_FOR"
IS
  'Default compression for what kind of operations';
  COMMENT ON TABLE "SYS"."DBA_TABLESPACES"
IS
  'Description of all tablespaces';
USER_TABLESPACES describes the tablespaces accessible to the current user.