Choose a Temporary Tablespace for a User

Every Oracle database user requires a temporary tablespace — a designated area on disk where the database engine writes intermediate data during operations that exceed available memory. Sort operations, hash joins, bitmap merge operations, and certain index builds all rely on temporary tablespace when their working sets are too large to fit in the Program Global Area (PGA). Assigning users to an appropriate temporary tablespace is a foundational DBA task that directly affects query performance for every user on the system.

Why Temporary Tablespace Assignment Matters

If a user is not assigned a temporary tablespace and no database-level default temporary tablespace has been configured, Oracle falls back to the SYSTEM tablespace for temporary operations. The SYSTEM tablespace hosts Oracle's data dictionary — the internal metadata that every SQL statement consults during parsing and execution. Mixing temporary sort data with data dictionary I/O on the same tablespace creates disk contention, degrading performance not just for the user running the sort, but for every other session that accesses the database concurrently.

The contrast is straightforward. A user assigned to a dedicated TEMP tablespace performs sort operations against a set of disks isolated from other activity. Their sort progresses at full I/O throughput. A user without a temporary tablespace assignment performs that same sort against SYSTEM, competing directly with dictionary lookups from all other active sessions. In a busy OLTP database, this single misconfiguration can make the entire system appear to stall.

Assigning a Temporary Tablespace at User Creation

The temporary tablespace is specified with the TEMPORARY TABLESPACE clause of CREATE USER:

CREATE USER app_user IDENTIFIED BY SecurePass#2024
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

Most databases are created with at least one temporary tablespace, typically named TEMP. For most users, assigning this shared temporary tablespace is sufficient. For users running very large analytical queries or batch jobs with heavy sort requirements, consider creating a dedicated temporary tablespace to isolate their I/O from other users.

The temporary tablespace assignment can be changed at any time with ALTER USER:

ALTER USER app_user TEMPORARY TABLESPACE temp2;

No quota is required on a temporary tablespace — space in a temporary tablespace is managed automatically by Oracle and is never permanently allocated to a user.

Creating and Managing Temporary Tablespaces

Temporary tablespaces use tempfiles rather than standard datafiles. Tempfiles behave differently from datafiles in one important respect: on some platforms, a newly created tempfile may not immediately appear to occupy its full allocated size on disk. This is normal behavior — the space is reserved but not necessarily written until Oracle actually needs it. Do not interpret a tempfile that appears smaller than its allocated size as an error.

Create a temporary tablespace:

CREATE TEMPORARY TABLESPACE temp
  TEMPFILE '/u01/oradata/orcl/temp01.dbf' SIZE 500M AUTOEXTEND ON;

Drop a temporary tablespace and its associated tempfiles:

DROP TEMPORARY TABLESPACE temp
  INCLUDING CONTENTS AND DATAFILES;

Add a tempfile to an existing temporary tablespace to increase capacity:

ALTER TABLESPACE temp
  ADD TEMPFILE '/u01/oradata/orcl/temp02.dbf' SIZE 500M AUTOEXTEND ON;

Setting the Database-Level Default Temporary Tablespace

Oracle allows a DBA to set a database-wide default temporary tablespace that applies to any user not explicitly assigned one. This is configured with the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE, or modified after database creation:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

When a new default temporary tablespace is assigned at the database level, all users not explicitly assigned a temporary tablespace are automatically switched to the new default. The current default temporary tablespace is visible in DATABASE_PROPERTIES:

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

A default temporary tablespace cannot be dropped or taken offline while it is designated as the database default. Assign a replacement first, then drop or offline the former one. A temporary tablespace also cannot be converted to a permanent tablespace.

Temporary Tablespace Groups

Oracle supports temporary tablespace groups, which allow multiple temporary tablespaces to be managed as a single unit and assigned to users or set as the database default. Tablespace groups are useful in large databases where a single temporary tablespace cannot provide sufficient I/O throughput for concurrent workloads.

-- Create temporary tablespaces and assign them to a group
CREATE TEMPORARY TABLESPACE temp1
  TEMPFILE '/u01/oradata/orcl/temp1_01.dbf' SIZE 500M
  TABLESPACE GROUP temp_group;

CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/oradata/orcl/temp2_01.dbf' SIZE 500M
  TABLESPACE GROUP temp_group;

-- Assign the group as the database default
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;

When a user is assigned a tablespace group rather than a single temporary tablespace, Oracle distributes their temporary segments across the tablespaces in the group, providing parallel I/O paths for large sort operations.

Monitoring Temporary Tablespace Usage

Several views allow DBAs to monitor how temporary tablespace is being consumed. To check allocated and free space in temporary tablespaces:

SELECT tablespace_name,
  tablespace_size / 1024 / 1024 AS size_mb,
  free_space / 1024 / 1024 AS free_mb,
  (tablespace_size - free_space) / 1024 / 1024 AS used_mb
FROM dba_temp_free_space
ORDER BY tablespace_name;

To identify active sessions currently consuming temporary space:

SELECT s.sid,
  s.serial#,
  s.username,
  s.osuser,
  s.status,
  u.tablespace,
  u.blocks * 8192 / 1024 / 1024 AS temp_mb_used
FROM v$session s,
     v$tempseg_usage u
WHERE s.saddr = u.session_addr
ORDER BY temp_mb_used DESC;

To view tempfile allocation details:

SELECT tablespace_name,
  file_name,
  bytes / 1024 / 1024 AS size_mb,
  autoextensible,
  maxbytes / 1024 / 1024 AS max_mb
FROM dba_temp_files
ORDER BY tablespace_name;

Global Temporary Tables and Temporary Tablespace

Global temporary tables (GTTs) are a related but distinct concept from the temporary tablespace. A GTT is a persistent table definition stored in the data dictionary, but its data exists only for the duration of a session or transaction, depending on how it was created. GTT data is written to the temporary tablespace of the session that populates it, which is why appropriate temporary tablespace sizing is important in workloads that use GTTs heavily.

Two creation options govern GTT data lifetime:

-- Data cleared at each COMMIT
CREATE GLOBAL TEMPORARY TABLE temp_results (
  id     NUMBER,
  result VARCHAR2(200))
ON COMMIT DELETE ROWS;

-- Data retained until session ends
CREATE GLOBAL TEMPORARY TABLE temp_results (
  id     NUMBER,
  result VARCHAR2(200))
ON COMMIT PRESERVE ROWS;

GTT data is session-specific — a session cannot see rows inserted into a GTT by another session. This isolation is by design and explains why querying a GTT from a different connection than the one that populated it returns no rows.

From Oracle 12c onward, private optimizer statistics can be gathered for GTTs at the session level, allowing the Cost Based Optimizer to make more accurate cardinality estimates for queries against session-specific data:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APP_USER',
  tabname => 'TEMP_RESULTS',
  no_invalidate => FALSE);

In Oracle 23ai, automatic statistics management for GTTs is further refined, reducing the need for manual statistics gathering in most workloads.

Cost Based Optimizer and Temporary Tablespace

The Oracle Cost Based Optimizer (CBO) uses statistics about tables, indexes, and data distribution to estimate the cost of candidate execution plans and select the most efficient one. Temporary tablespace is consumed when the CBO chooses plans involving sort operations or hash joins that exceed PGA allocation limits. In Oracle 19c and later, the Adaptive Query Optimization framework can adjust plans mid-execution based on actual row counts, but large sort and hash operations will still spill to temporary tablespace when working sets are large.

For workloads where large sorts are frequent, increasing the PGA_AGGREGATE_TARGET or PGA_AGGREGATE_LIMIT can reduce temporary tablespace consumption by allowing more sort work to occur in memory:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 4G SCOPE = BOTH;

This does not eliminate the need for a properly sized temporary tablespace — it reduces the frequency with which sort operations must spill to disk. Temporary tablespace should always be sized generously enough to accommodate the largest anticipated concurrent sort workload.

Oracle 23ai Considerations

Oracle 23ai introduces no fundamental changes to the temporary tablespace model, but several related enhancements affect how temporary space is consumed. The new SQL domain and annotation features can influence query plan shape, and the enhanced Automatic Shared Memory Management in 23ai may reduce PGA spill-to-disk frequency for well-tuned systems. DBAs migrating to 23ai should review temporary tablespace sizing after workload profiling, as memory management improvements may change the balance between in-memory and on-disk sort operations.

Summary

Assigning every user an explicit temporary tablespace is a non-negotiable baseline for any production Oracle database. Without it, sort and hash join operations fall back to the SYSTEM tablespace, creating disk contention that degrades performance for all concurrent users. Assign the shared TEMP tablespace to standard users, consider dedicated temporary tablespaces or tablespace groups for high-volume analytical workloads, and monitor consumption through DBA_TEMP_FREE_SPACE and V$TEMPSEG_USAGE. Size temporary tablespace generously enough to accommodate peak concurrent sort loads, and tune PGA_AGGREGATE_TARGET to reduce the frequency of disk spill where possible.