You can use the user_tablespaces dictionary view to get the tablespace used by a user in Oracle. Below is an example:
SQL Query to Find Tablespace Used by a user in Oracle
The following SQL query will not only return the tablespace name but more other useful information regarding a tablespace used by a user in Oracle.
Select tablespace_name, block_size, initial_extent, next_extent, status From user_tablespaces;
Output
Even the more column exists for Oracle dictionary view user_tablespaces. Below is the list of all columns:
user_tablespaces Structure
| Column | Description |
| TABLESPACE_NAME | Tablespace name |
| BLOCK_SIZE | Tablespace block size |
| INITIAL_EXTENT | Default initial extent size |
| NEXT_EXTENT | Default incremental extent size |
| MIN_EXTENTS | Default minimum number of extents |
| MAX_EXTENTS | Default maximum number of extents |
| MAX_SIZE | Default maximum size of segments |
| PCT_INCREASE | Default percent increase for extent size |
| MIN_EXTLEN | Minimum extent size for the tablespace |
| STATUS | Tablespace status: “ONLINE”, “OFFLINE”, or “READ ONLY” |
| CONTENTS | Tablespace contents: “PERMANENT”, or “TEMPORARY” |
| LOGGING | Default logging attribute |
| FORCE_LOGGING | Tablespace force logging mode |
| EXTENT_MANAGEMENT | Extent management tracking: “DICTIONARY” or “LOCAL” |
| ALLOCATION_TYPE | Type of extent allocation in effect for this tablespace |
| SEGMENT_SPACE_MANAGEMENT | Segment space management tracking: “AUTO” or “MANUAL” |
| DEF_TAB_COMPRESSION | Default table compression enabled or not: “ENABLED” or “DISABLED” |
| RETENTION | Undo tablespace retention: “GUARANTEE”, “NOGUARANTEE” or “NOT APPLY” |
| BIGFILE | Bigfile tablespace indicator: “YES” or “NO” |
| PREDICATE_EVALUATION | Predicates evaluated by: “HOST” or “STORAGE” |
| ENCRYPTED | Encrypted tablespace indicator: “YES” or “NO” |
| COMPRESS_FOR | Default compression for what kind of operations |
| DEF_INMEMORY | Default in-memory attribute |
| DEF_INMEMORY_PRIORITY | Default priority in which in-memory column store objects are loaded |
| DEF_INMEMORY_DISTRIBUTE | Default for how in-memory columnar store objects are distributed |
| DEF_INMEMORY_COMPRESSION | Default compression level for the in-memory column store option |
| DEF_INMEMORY_DUPLICATE | Default for how in-memory column store objects are duplicated |
| SHARED | Local temp tablespace type: “LOCAL_ON_LEAF” or “LOCAL_ON_ALL” or “SHARED” |
| DEF_INDEX_COMPRESSION | Default index compression enabled or not: “ENABLED” or “DISABLED” |
| INDEX_COMPRESS_FOR | Default index compression level: “ADVANCED HIGH/LOW” or “NONE” |
| DEF_CELLMEMORY | Default for columnar compression in storage cell flash cache |
| DEF_INMEMORY_SERVICE | How the in-memory columnar store objects are distributed for service |
| DEF_INMEMORY_SERVICE_NAME | Service on which the in-memory columnar store objects are distributed |
| LOST_WRITE_PROTECT | LOST_WRITE indicator: “OFF” or “ENABLED” or “SUSPEND” – also check dba_data_files |
| CHUNK_TABLESPACE | – |
If you want to know the tablespace name used by a particular table, use the user_tables dictionary view. The following is an example SQL query:
Select
tablespace_name
From
user_tables
Where
table_name = 'EMP';
Check this post find tablespace in SQL developer to learn how to find tablespace using SQL developer in Oracle.
Leave a comment