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.
Wow, great blog post. Much thanks again. Will read on…
Interesting blog. It would be great if you can provide more details about it. Thanks you