Monitoring Temporary Tablespace Usage in Oracle Database
Lists the Contents of the Temporary Tablespace(s), Including Details About Each Temporary Object and Its Associated Session
v$sort_usage is the view to open when TEMP fills up: it holds one row per temporary segment in use, with the user, the session, the tablespace and the blocks allocated. Whether the trigger is an ORA-01652: unable to extend temp segment in a night batch or a space alert on a quiet afternoon, it answers which sessions hold temporary space right now, and how much.
Purpose
This script lists every session that has temporary space allocated, with the tablespace, the space in megabytes and the type of segment. It reads v$sort_usage, which reports temporary segment usage per session, and joins dba_tablespaces to turn a block count into megabytes using each tablespace's block size.
Oracle's Administrator's Guide lists what lives in a temporary tablespace: intermediate sort results, temporary tables and temporary indexes, temporary LOBs and temporary B-trees. So "temp is full" is not always a big sort. It can be a hash join, a global temporary table or a LOB built in a loop, and the segment type column tells you which.
Code
1set lines 100 pages 999
2col username format a20
3col tablespace format a20
4col mb format 999,999,999
5select s.username || '(' || s.session_num || ')' username
6, s.tablespace
7, ceil((s.blocks * t.block_size) /1024/1024) "MB"
8, s.segtype
9from v$sort_usage s
10, dba_tablespaces t
11where s.tablespace = t.tablespace_name
12order by s.blocks desc
13/
To see the session's SID and serial number, so you can find it in v$session or end it, join on the session address:
1set pages 999 lines 100
2col username format a15
3col mb format 999,999
4select su.username
5, ses.sid
6, ses.serial#
7, su.tablespace
8, su.segtype
9, su.sql_id
10, ceil((su.blocks * dt.block_size) / 1048576) MB
11from v$sort_usage su
12, dba_tablespaces dt
13, v$session ses
14where su.tablespace = dt.tablespace_name
15and su.session_addr = ses.saddr
16order by su.blocks desc
17/
Code Breakdown
SQL*Plus settings
set lines 100 pages 999 widens the line and makes headings repeat less often. The col ... format lines set column widths so the output fits on one line per row, and 999,999,999 puts thousands separators in the MB column.
The username column
s.username || '(' || s.session_num || ')' prints the user with the session serial number in brackets, for example APP_OWNER(1243). The serial number tells two sessions of the same user apart.
Blocks to megabytes
v$sort_usage reports space in blocks, not bytes. dba_tablespaces.block_size holds each tablespace's block size in bytes, so blocks * block_size / 1024 / 1024 is the space in MB. ceil rounds up so a small allocation shows as 1 MB rather than 0.
Segment type
segtype says what the space is for. Oracle documents six values: SORT, HASH, DATA, INDEX, LOB_DATA and LOB_INDEX. SORT and HASH come from queries; DATA and INDEX usually mean global temporary tables; the LOB types mean temporary LOBs.
The session join
The second query joins su.session_addr to ses.saddr, the session address in v$session, to add sid and serial#. It also adds sql_id, the SQL identifier of the statement, which you can look up in v$sql.
Key Points
v$sort_usageis the older name. The current Oracle Database Reference documents the view asV$TEMPSEG_USAGE, which "describes temporary segment usage" and has the same columns used here. The script runs with either name.- The output is a snapshot. It shows space held at the moment you run it. A query that finished a second earlier is already gone.
- Freed space stays allocated. Oracle notes that when a statement that used temporary space completes, its extents are not deallocated; they are marked free and available for reuse. A tempfile that grew last night stays large today.
- Privileges. The
v$anddba_views need a DBA-level account orSELECTon each view. - Multitenant. In a CDB,
v$tempseg_usageincludes aCON_IDcolumn, so run the query in the PDB you are investigating or filter oncon_idfrom the root.
Insights and Best Practices
Check the tablespace totals as well
Per-session usage tells you who holds space. DBA_TEMP_FREE_SPACE tells you how full the tablespace is. Oracle's reference lists its columns as TABLESPACE_SIZE, ALLOCATED_SPACE and FREE_SPACE, all in bytes:
1select tablespace_name
2, ceil(tablespace_size / 1048576) size_mb
3, ceil(allocated_space / 1048576) allocated_mb
4, ceil(free_space / 1048576) free_mb
5from dba_temp_free_space
6/
A tablespace with high ALLOCATED_SPACE but high FREE_SPACE is not under pressure; the space is allocated and ready for reuse.
Read the segment type before acting
A large HASH segment points at a hash join that spilled to disk, so look at the plan for that sql_id. A large DATA segment owned by an application user usually means a global temporary table filled by a batch job. The fixes differ: tune the query in the first case, look at the job's design in the second.
Run it during the problem, not after
Because the view only shows space held now, run the script while the slowdown or the space alert is happening. For space that was used and released earlier, use Active Session History instead: v$active_session_history records TEMP_SPACE_ALLOCATED, the temporary space in bytes each sampled session was using at the time of the sample, so you can find which sql_id drove TEMP up at 02:00 last night even though the session has long since ended. The ASH data is part of the Diagnostic Pack.
Shrink only when you must
If a one-off job grew TEMP far beyond its normal size, Oracle lets you shrink a locally managed temporary tablespace with ALTER TABLESPACE ... SHRINK SPACE. Shrinking a tablespace that the normal workload needs only makes it grow again.
When to Run This
- A job fails with
ORA-01652and you need to know which session filled TEMP. - A space alert fires on the temporary tablespace.
- A report runs slowly and you suspect sorts or hash joins spilling to disk.
- Before ending sessions to free space, to choose the right session.
Troubleshooting Common Issues
If the query returns no rows, no session holds temporary space at that moment, which is normal on a quiet system. If a session appears in the first query but not in the second, it may have ended between the two runs. If the MB figures look far too small, check that the tablespace in v$sort_usage matches a row in dba_tablespaces; a typo in a filter you added, or a temporary tablespace group, can drop rows. If ORA-01652 keeps recurring, compare DBA_TEMP_FREE_SPACE with the largest single session in the output: one session using most of TEMP points to one statement to tune, while many medium sessions point to a tablespace that is too small for the concurrent load.
References
- shutdownabort.com — Structure: Tablespace (Wayback, 2007-12-17) - Original source of the "See who's using the temp tablespace" query, from Andrew Barry's DBA Quick Guides.
- V$TEMPSEG_USAGE - Oracle Database Reference 19c - Columns of the temporary segment view, including SEGTYPE values, SESSION_ADDR and SQL_ID.
- DBA_TEMP_FREE_SPACE - Oracle Database Reference 19c - Tablespace-level size, allocated space and free space for temporary tablespaces.
- Managing Tablespaces - Oracle Database Administrator's Guide 19c - What temporary tablespaces store, how sort segment extents are reused, and shrinking a temporary tablespace.
- V$ACTIVE_SESSION_HISTORY - Oracle Database Reference 19c - Includes TEMP_SPACE_ALLOCATED per sampled session.
- Temporary Tablespace Enhancements in Oracle Database 11g - oracle-base.com - Worked examples of DBA_TEMP_FREE_SPACE and ALTER TABLESPACE ... SHRINK SPACE.
Posts in this series
- Monitoring Temporary Tablespace Usage in Oracle Database
- Adjusting Segment Growth with MAXEXTENTS in Oracle Database
- Oracle Segments Nearing Max Extents via dba_segments
- Setting a Default Temporary Tablespace in Oracle Database
- Identifying User Tablespaces and Space Usage in Oracle
- Listing Objects in an Oracle Tablespace
- Oracle Tablespaces Needing More Space: SQL Script
- Exploring Datafiles within Oracle Tablespaces
- Understanding Tablespace Usage in Oracle Database
- Understanding User Quotas in Oracle Database
- Creating a Temporary Tablespace in Oracle Database