Temporary Tablespaces
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 TEMP | Example |
|---|---|
| Sorting | ORDER BY, GROUP BY, DISTINCT on big data |
| Joins | Hash joins that do not fit in memory |
| Index builds | CREATE INDEX on a large table |
| Global temporary tables | Session 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
- Find the session using the most temp with the query above.
- Look at its SQL: is a join missing a condition (a Cartesian product)? Tune it.
- Add temp space if the work is legitimate.
- Raise
pga_aggregate_targetso 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.

