Temporary Tablespaces

Oracle DBA Certification 10 min min read Updated: Nov 16, 2026 Intermediate
Temporary Tablespaces
Intermediate Topic 4 of 6

What is TEMP?

When a sort or hash join is too big for PGA memory, Oracle spills it to the temporary tablespace. Temp data lives only as long as the operation or session needs it.

Uses TEMPExample
SortingORDER BY, GROUP BY, DISTINCT on big data
JoinsHash joins that do not fit in memory
Index buildsCREATE INDEX on a large table
Global temporary tablesSession data

Look at Temp Files

sql SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS mb, autoextensible FROM dba_temp_files; SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

Create a Temporary Tablespace

sql CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/temp02.dbf' SIZE 200M AUTOEXTEND ON NEXT 100M MAXSIZE 4G; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Why not just resize TEMP? Creating a new temp tablespace and switching to it is the easy way to shrink an oversized TEMP: make temp2, set it as default, then drop the old one.

Who is Using TEMP?

sql SELECT s.sid, s.username, s.sql_id, ROUND(u.blocks * 8192 / 1024 / 1024) AS mb_used, u.tablespace FROM v$tempseg_usage u JOIN v$session s ON s.saddr = u.session_addr ORDER BY u.blocks DESC;
Tip: The multiplier 8192 assumes an 8 KB block size. Check yours with SHOW PARAMETER db_block_size.

Add Space

sql ALTER TABLESPACE temp ADD TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/temp03.dbf' SIZE 500M;

ORA-01652: Unable to Extend Temp Segment

  1. Find the session using the most temp with the query above.
  2. Look at its SQL: is a join missing a condition (a Cartesian product)? Tune it.
  3. Add temp space if the work is legitimate.
  4. Raise pga_aggregate_target so more sorts stay in memory.

Temp Tablespace Groups and Users

sql ALTER USER shop TEMPORARY TABLESPACE temp;

Every user should point to a temporary tablespace, never to SYSTEM.

Conclusion

TEMP holds big sorts and hash joins that spill out of memory. Watch V$TEMPSEG_USAGE, tune the SQL that over-uses it and add space only when needed.

Quick Summary: Temp = scratch space. Check V$TEMPSEG_USAGE. Fix the SQL first, then add space.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators