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_usage is the older name. The current Oracle Database Reference documents the view as V$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$ and dba_ views need a DBA-level account or SELECT on each view.
  • Multitenant. In a CDB, v$tempseg_usage includes a CON_ID column, so run the query in the PDB you are investigating or filter on con_id from 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-01652 and 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

Posts in this series