Choose a Temporary Tablespace for a User in Oracle AI Database 26ai

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 the container has no default temporary tablespace 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, and 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 Default Temporary Tablespace

Oracle allows a DBA to set a 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, 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';

As covered in the previous lesson, this setting is per container, not database wide in the sense the name might suggest. Since every Oracle AI Database 26ai database is a multitenant container database, DATABASE_PROPERTIES reflects only the container you are currently connected to. Each PDB has its own default temporary tablespace, independent of every other PDB and of the CDB root. If you need to see this setting across an entire container at once, query CDB_PROPERTIES instead.

A default temporary tablespace cannot be dropped or taken offline while it is designated as the 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 container 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 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;

One small detail worth knowing if you browse this view directly: V$TEMPSEG_USAGE also has a column literally named USER, which is obsolete and kept only for backward compatibility; it always holds the same value as USERNAME, so use USERNAME in new queries.

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.

Private Temporary Tables: A Lighter-Weight Alternative

Worth knowing alongside the standard GTT is a related, distinct feature: the private temporary table. Where a regular GTT's definition is a permanent object in the data dictionary and only its data is session specific, a private temporary table keeps both its definition and its data visible only within the session that created it, leaving nothing permanent behind at all once the session ends:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_scratch (
 id     NUMBER,
 result VARCHAR2(200))
ON COMMIT PRESERVE DEFINITION;

The ON COMMIT clause works a little differently here than for a standard GTT. DROP DEFINITION, the default, drops both the data and the table definition itself when the transaction commits, producing a transaction-specific table. PRESERVE DEFINITION keeps the definition in place across commits within the session, producing a session-specific table. Only CREATE, DROP, and TRUNCATE are supported against private temporary tables, and their names must always carry whatever prefix your session's PRIVATE_TEMP_TABLE_PREFIX setting defines, ora$ in the example above being Oracle's own default. This is a genuinely useful option when you want a temporary working table for a single ad hoc task without leaving even a table definition behind in the dictionary afterward.

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. Session-private optimizer statistics can be gathered for GTTs, letting the CBO make more accurate cardinality estimates for queries against session-specific data, though large sort and hash operations will still spill to temporary tablespace when working sets are genuinely large regardless of statistics quality.

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

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.

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. Remember that the default temporary tablespace setting is scoped to a single container in a multitenant database, not the CDB as a whole. 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.