Introduction
A common Oracle database incident occurs when the filesystem or mount point containing the TEMP tablespace tempfiles reaches 100% utilisation.
At first glance, the situation can appear straightforward:
Filesystem 100% full
|
+-- TEMP tempfile 1
+-- TEMP tempfile 2
However, TEMP space management is different from normal permanent tablespace management.
A tempfile may have grown significantly because of a large sort, hash join, index operation, temporary LOB or other workload. After that SQL completes, Oracle can reuse the temporary extents internally, but the physical tempfile generally remains at its expanded size.
Therefore, these are two different questions:
1. How large are the TEMP files on disk?
2. How much TEMP is actually being consumed right now?
Oracle 10g documentation explains that temporary tablespace sort operations share sort segments and that V$SORT_SEGMENT can be used to examine allocation while V$TEMPSEG_USAGE identifies current TEMP users. The sort segment itself can persist after operations have completed.
This article provides a logical troubleshooting sequence covering:
filesystem utilisation;
TEMP tablespace configuration;
tempfile size and autoextend configuration;
allocated versus reusable TEMP;
active TEMP consumers;
source machines and application programs;
SQL statements;
TEMP segment types;
historical TEMP growth;
AWR/ASH investigation;
Oracle 10g limitations;
Oracle 19c enhancements;
safe ways to reclaim filesystem space;
prevention of recurrence.
Important: Never manually delete an Oracle tempfile at operating-system level while Oracle still has it registered in the database.
1. Confirm That the Filesystem Is Actually Full
Start outside Oracle and confirm the filesystem containing the tempfiles.
On Linux:
df -h <temp_mount_point>
You may see a situation such as:
Filesystem Size Used Avail Use%
<filesystem> ... ... 0 100%
The first objective is to determine whether Oracle TEMP is responsible for the majority of that filesystem consumption.
Do not immediately:
rm <tempfile>
Oracle still knows about the tempfile through its control file and data dictionary. Removing it directly from the operating system can create a more serious database problem.
2. Identify the Oracle Temporary Tablespace
First identify the database default temporary tablespace:
SELECT property_name,
property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
Also list all temporary tablespaces:
SELECT tablespace_name,
contents,
status,
extent_management,
allocation_type
FROM dba_tablespaces
WHERE contents = 'TEMPORARY';
It is possible for a database to contain multiple temporary tablespaces or temporary tablespace groups, so do not assume that the tablespace is always named TEMP.
3. Identify Every Tempfile and Its Maximum Growth
This query works for Oracle 10g and Oracle 19c:
SELECT
f.tablespace_name,
f.file_id,
f.file_name,
ROUND(f.bytes / 1024 / 1024 / 1024, 2) AS current_gb,
f.autoextensible,
ROUND(f.maxbytes / 1024 / 1024 / 1024, 2) AS max_gb,
ROUND(
f.increment_by * t.block_size /
1024 / 1024,
2
) AS next_mb
FROM dba_temp_files f
JOIN dba_tablespaces t
ON t.tablespace_name = f.tablespace_name
ORDER BY f.tablespace_name,
f.file_id;
Pay particular attention to:
CURRENT_GB
AUTOEXTENSIBLE
MAX_GB
NEXT_MB
For example, two tempfiles could currently occupy:
TEMP01 18 GB
TEMP02 14 GB
----------------
Total 32 GB
but both might have:
AUTOEXTEND = YES
MAXSIZE = 30 GB
meaning Oracle could potentially allow TEMP to grow to approximately:
30 GB + 30 GB = 60 GB
If the filesystem cannot safely accommodate that size, the current autoextend configuration itself represents a capacity risk.
4. Check TEMP Usage Reported by the Tempfile Space Header
For Oracle 10g and Oracle 19c:
SELECT
tablespace_name,
ROUND(SUM(bytes_used) / 1024 / 1024 / 1024, 2)
AS used_gb,
ROUND(SUM(bytes_free) / 1024 / 1024 / 1024, 2)
AS free_gb,
ROUND(
SUM(bytes_used + bytes_free) /
1024 / 1024 / 1024,
2
) AS total_gb
FROM v$temp_space_header
GROUP BY tablespace_name;
To see each tempfile individually:
SELECT
tablespace_name,
file_id,
ROUND(bytes_used / 1024 / 1024 / 1024, 2)
AS used_gb,
ROUND(bytes_free / 1024 / 1024 / 1024, 2)
AS free_gb,
ROUND(
(bytes_used + bytes_free) /
1024 / 1024 / 1024,
2
) AS total_gb
FROM v$temp_space_header
ORDER BY tablespace_name,
file_id;
V$TEMP_SPACE_HEADER reports used and free space recorded in each locally managed tempfile's space header.
However, this is where TEMP troubleshooting often goes wrong.
Suppose this query reports:
TABLESPACE USED_GB FREE_GB TOTAL_GB
---------- ------- ------- --------
TEMP 29.20 0.10 29.30
It is tempting to conclude:
Active SQL is currently consuming 29.2 GB.
That conclusion may be incorrect.
Oracle can retain TEMP extents inside its sort segment after the SQL that originally needed them has completed. Those extents may already be reusable.
Therefore, continue the investigation before increasing TEMP or killing sessions.
5. Determine How Much TEMP Is Actually Reusable
Oracle 10g – Use V$SORT_SEGMENT
This is one of the most important checks for Oracle 10g:
SELECT
s.tablespace_name,
s.current_users,
s.total_extents,
s.used_extents,
s.free_extents,
ROUND(
s.total_blocks * t.block_size /
1024 / 1024 / 1024,
2
) AS total_gb,
ROUND(
s.used_blocks * t.block_size /
1024 / 1024 / 1024,
2
) AS active_used_gb,
ROUND(
s.free_blocks * t.block_size /
1024 / 1024 / 1024,
2
) AS reusable_free_gb
FROM v$sort_segment s
JOIN dba_tablespaces t
ON t.tablespace_name = s.tablespace_name
ORDER BY s.tablespace_name;
V$SORT_SEGMENT provides counters including:
CURRENT_USERS
TOTAL_EXTENTS
USED_EXTENTS
FREE_EXTENTS
USED_BLOCKS
FREE_BLOCKS
Oracle defines USED_EXTENTS as extents allocated to active sorts and FREE_EXTENTS as extents not currently allocated to a sort.
For example:
TOTAL_GB 29.20
ACTIVE_USED_GB 0.01
REUSABLE_FREE_GB 29.19
This means the operating-system files may still occupy roughly 29 GB, while almost all that space is available for Oracle to reuse.
That is very different from having 29 GB of active SQL TEMP usage.
6. Identify Who Is Using TEMP Right Now
V$TEMPSEG_USAGE is the primary view for identifying current TEMP consumers.
Oracle documents this view as describing temporary segment usage, including user, session, SQL, segment type, extents and blocks.
The following query is compatible with Oracle 10g:
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.machine,
s.program,
s.module,
u.sql_id AS temp_sql_id,
s.sql_id AS current_sql_id,
u.tablespace,
u.segtype,
u.extents,
ROUND(
u.blocks * t.block_size /
1024 / 1024 / 1024,
3
) AS temp_used_gb
FROM v$tempseg_usage u
JOIN v$session s
ON s.saddr = u.session_addr
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
ORDER BY temp_used_gb DESC;
This provides the most useful operational information:
SID
SERIAL#
USERNAME
STATUS
MACHINE
PROGRAM
MODULE
TEMP_SQL_ID
CURRENT_SQL_ID
SEGTYPE
TEMP_USED_GB
The MACHINE and PROGRAM fields are particularly valuable because they help identify the application server, batch process, reporting server, SQL client or other source responsible for the connection.
Oracle 10g V$SESSION includes fields such as USERNAME, OSUSER, MACHINE, PROGRAM, SQL_ID, MODULE and session status.
7. Summarise TEMP Consumption by Username
For a quick high-level view:
SELECT
s.username,
ROUND(
SUM(u.blocks * t.block_size) /
1024 / 1024 / 1024,
3
) AS temp_used_gb
FROM v$tempseg_usage u
JOIN v$session s
ON s.saddr = u.session_addr
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
GROUP BY s.username
ORDER BY temp_used_gb DESC;
This can quickly reveal whether one application schema dominates TEMP usage.
8. Identify TEMP Usage by Program and Source Machine
For application troubleshooting, group TEMP consumption by database user, source machine and program:
SELECT
s.username,
s.machine,
s.program,
s.module,
ROUND(
SUM(u.blocks * t.block_size) /
1024 / 1024 / 1024,
3
) AS temp_used_gb
FROM v$tempseg_usage u
JOIN v$session s
ON s.saddr = u.session_addr
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
GROUP BY
s.username,
s.machine,
s.program,
s.module
ORDER BY temp_used_gb DESC;
This is useful where many sessions use the same Oracle schema but originate from different application servers or programs.
9. Check All Active Database Sessions
TEMP may be growing because of a currently executing workload even before a large allocation becomes obvious.
To show active user sessions:
SELECT
sid,
serial#,
username,
status,
machine,
program,
module,
action,
sql_id,
event,
wait_class
FROM v$session
WHERE status = 'ACTIVE'
AND type = 'USER'
ORDER BY username,
machine,
program;
To count active sessions by application:
SELECT
username,
machine,
program,
module,
COUNT(*) AS active_sessions
FROM v$session
WHERE status = 'ACTIVE'
AND type = 'USER'
GROUP BY
username,
machine,
program,
module
ORDER BY active_sessions DESC;
This can reveal issues such as:
Application batch process 25 active sessions
Reporting application 10 active sessions
SQL client 2 active sessions
A sudden increase in concurrent reporting or batch operations can dramatically increase TEMP requirements.
10. Identify the SQL Currently Using TEMP
Use the SQL ID returned by V$TEMPSEG_USAGE.
For current SQL:
SELECT
sql_id,
child_number,
sql_text
FROM v$sql
WHERE sql_id = '<SQL_ID>';
For more of the SQL text:
SET LONG 10000
SET LONGCHUNKSIZE 10000
SELECT
sql_id,
sql_fulltext
FROM v$sql
WHERE sql_id = '<SQL_ID>';
Common operations that can consume significant TEMP include large:
ORDER BY;GROUP BY;DISTINCT;hash joins;
analytic functions;
index creation or rebuilds;
CREATE TABLE AS SELECT;parallel query operations;
temporary LOB processing;
materialisation and intermediate query results.
TEMP usage often occurs when an operation cannot complete entirely within its available PGA work area and spills work to disk.
11. Identify the Types of TEMP Segments Being Used
Run:
SELECT
u.segtype,
COUNT(*) AS allocations,
SUM(u.extents) AS extents,
ROUND(
SUM(u.blocks * t.block_size) /
1024 / 1024 / 1024,
3
) AS used_gb
FROM v$tempseg_usage u
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
GROUP BY u.segtype
ORDER BY used_gb DESC;
Typical segment types include:
| SEGTYPE | Typical meaning |
|---|---|
SORT | Disk-based sort operations |
HASH | Hash operations such as hash joins |
DATA | Temporary data segments |
INDEX | Temporary index-related segments |
LOB_DATA | Temporary LOB data |
LOB_INDEX | Temporary LOB index structures |
Oracle documents these segment types in V$TEMPSEG_USAGE.
This can be extremely useful.
For example:
SEGTYPE USED_GB
----------- -------
HASH 18.50
SORT 4.20
LOB_DATA 0.01
would point investigation strongly toward SQL involving large hash operations.
12. Why TEMP Can Look Full Even When No Session Is Using It
This is a critical Oracle TEMP concept.
A possible sequence is:
Large query begins
|
v
Query requires 20 GB TEMP
|
v
TEMP files autoextend
|
v
Query completes
|
v
Session releases TEMP extents
|
v
Oracle keeps extents available for reuse
|
v
Physical tempfile remains large
|
v
Filesystem remains full
Therefore:
Filesystem TEMP size
is not necessarily equal to:
Current active TEMP consumption
This explains why V$TEMP_SPACE_HEADER can appear nearly full while V$TEMPSEG_USAGE shows almost no current users.
13. Check Historical TEMP Growth – Oracle 10g
When the SQL that caused the incident has already completed, current views cannot identify it.
At that point, historical AWR/ASH data can be extremely valuable.
Licensing Warning
Oracle AWR and ASH functionality is associated with Oracle Diagnostics Pack licensing. Oracle's licensing documentation states that AWR, ASH and most DBA_HIST_* views require the Diagnostics Pack. Confirm that your organisation is appropriately licensed before using these features.
14. Find When the TEMP Tablespace Grew
Oracle 10g includes DBA_HIST_TBSPC_SPACE_USAGE, which records historical tablespace size information.
Use:
WITH snap_times AS
(
SELECT
snap_id,
dbid,
MIN(begin_interval_time) AS begin_time,
MAX(end_interval_time) AS end_time
FROM dba_hist_snapshot
GROUP BY snap_id,
dbid
),
temp_history AS
(
SELECT
u.snap_id,
st.begin_time,
st.end_time,
ROUND(
u.tablespace_size * dt.block_size /
1024 / 1024 / 1024,
2
) AS size_gb,
ROUND(
u.tablespace_usedsize * dt.block_size /
1024 / 1024 / 1024,
2
) AS used_gb
FROM dba_hist_tbspc_space_usage u
JOIN snap_times st
ON st.snap_id = u.snap_id
AND st.dbid = u.dbid
JOIN v$tablespace vt
ON vt.ts# = u.tablespace_id
JOIN dba_tablespaces dt
ON dt.tablespace_name = vt.name
WHERE vt.name = 'TEMP'
AND st.begin_time >= SYSDATE - 7
)
SELECT
snap_id,
TO_CHAR(begin_time,
'YYYY-MM-DD HH24:MI') AS begin_time,
TO_CHAR(end_time,
'YYYY-MM-DD HH24:MI') AS end_time,
size_gb,
used_gb,
ROUND(
size_gb -
LAG(size_gb)
OVER (ORDER BY snap_id),
2
) AS growth_gb
FROM temp_history
ORDER BY snap_id;
Adjust:
SYSDATE - 7
to the required investigation period.
Look for a pattern such as:
TIME SIZE_GB GROWTH_GB
---------------- ------- ---------
10:00 10.00
11:00 10.00 0
12:00 18.00 8
13:00 30.00 12
14:00 30.00 0
This identifies the window during which TEMP expanded.
That time window can then be correlated with historical session activity.
15. Identify Historical Users, Programs and Machines – Oracle 10g
Oracle 10g ASH does not provide the later TEMP_SPACE_ALLOCATED column available in newer releases.
Therefore, on 10g the objective is to correlate:
TEMP growth time
+
historically active sessions
+
SQL IDs
+
machine/program
+
TEMP-related waits
+
sort/direct-write activity
Oracle 10g ASH captures sampled active session information and periodically persists a subset into AWR. Because it is sampled data, it should be treated as diagnostic evidence rather than a complete transaction log.
Set the investigation window:
DEFINE start_time = '2026-01-01 10:00:00'
DEFINE end_time = '2026-01-01 12:00:00'
Then:
SELECT
ash.instance_number,
ash.session_id AS sid,
ash.session_serial# AS serial#,
u.username,
ash.sql_id,
aa.name AS sql_operation,
ash.machine,
ash.program,
ash.module,
ash.action,
ash.client_id,
COUNT(*) AS ash_samples,
TO_CHAR(
MIN(ash.sample_time),
'YYYY-MM-DD HH24:MI:SS'
) AS first_seen,
TO_CHAR(
MAX(ash.sample_time),
'YYYY-MM-DD HH24:MI:SS'
) AS last_seen
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_users u
ON u.user_id = ash.user_id
LEFT JOIN audit_actions aa
ON aa.action = ash.sql_opcode
WHERE ash.sample_time >=
TO_TIMESTAMP(
'&start_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND ash.sample_time <
TO_TIMESTAMP(
'&end_time',
'YYYY-MM-DD HH24:MI:SS'
)
GROUP BY
ash.instance_number,
ash.session_id,
ash.session_serial#,
u.username,
ash.sql_id,
aa.name,
ash.machine,
ash.program,
ash.module,
ash.action,
ash.client_id
ORDER BY ash_samples DESC;
This can help identify:
Oracle username
SID / SERIAL#
SQL_ID
SQL operation
Source machine
Program
Module
Application action
First seen
Last seen
Do not use ASH.SQL_OPNAME on Oracle 10g if that column is not present in your DBA_HIST_ACTIVE_SESS_HISTORY definition.
Instead, use:
ASH.SQL_OPCODE
and map it through:
AUDIT_ACTIONS
as shown above.
16. Search Specifically for TEMP-Related Wait Events
During the identified growth window:
SELECT
TO_CHAR(
ash.sample_time,
'YYYY-MM-DD HH24:MI:SS'
) AS sample_time,
ash.instance_number,
ash.session_id AS sid,
ash.session_serial# AS serial#,
u.username,
ash.sql_id,
ash.machine,
ash.program,
ash.module,
ash.session_state,
ash.event,
ash.wait_class
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_users u
ON u.user_id = ash.user_id
WHERE ash.sample_time >=
TO_TIMESTAMP(
'&start_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND ash.sample_time <
TO_TIMESTAMP(
'&end_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND (
ash.event = 'direct path write temp'
OR ash.event = 'direct path read temp'
OR LOWER(ash.event) LIKE '%temp%'
)
ORDER BY ash.sample_time;
Repeated entries such as:
APP_USER
APP-SERVER
SQL_ID abc123...
direct path write temp
during exactly the same period in which the TEMP tablespace expanded provide strong evidence that the SQL was involved.
However, do not search only for TEMP wait events.
A TEMP-consuming SQL statement can be:
ON CPU
or waiting for another resource at the instant ASH samples it.
17. Retrieve the Historical SQL Text
After identifying a suspicious SQL_ID:
SET LONG 10000
SET LONGCHUNKSIZE 10000
SELECT
sql_id,
command_type,
DBMS_LOB.SUBSTR(
sql_text,
4000,
1
) AS sql_text
FROM dba_hist_sqltext
WHERE sql_id = '<SQL_ID>';
Also check whether the statement is still in memory:
SELECT
sql_id,
sql_fulltext
FROM v$sql
WHERE sql_id = '<SQL_ID>';
18. Rank Historical SQL by Sort and Direct-Write Activity – Oracle 10g
Oracle 10g DBA_HIST_SQLSTAT includes statistics such as:
SORTS_DELTA
DIRECT_WRITES_DELTA
EXECUTIONS_DELTA
CPU_TIME_DELTA
ELAPSED_TIME_DELTA
Oracle's 10g Database Reference documents both SORTS_DELTA and DIRECT_WRITES_DELTA.
First identify the relevant AWR snapshots:
SELECT
snap_id,
instance_number,
TO_CHAR(
begin_interval_time,
'YYYY-MM-DD HH24:MI:SS'
) AS begin_time,
TO_CHAR(
end_interval_time,
'YYYY-MM-DD HH24:MI:SS'
) AS end_time
FROM dba_hist_snapshot
WHERE begin_interval_time <
TO_TIMESTAMP(
'&end_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND end_interval_time >
TO_TIMESTAMP(
'&start_time',
'YYYY-MM-DD HH24:MI:SS'
)
ORDER BY snap_id,
instance_number;
Then investigate the SQL activity:
SELECT *
FROM
(
SELECT
s.sql_id,
SUM(s.executions_delta)
AS executions,
SUM(s.sorts_delta)
AS sorts,
SUM(s.direct_writes_delta)
AS direct_writes,
ROUND(
SUM(s.elapsed_time_delta) /
1000000,
2
) AS elapsed_seconds,
ROUND(
SUM(s.cpu_time_delta) /
1000000,
2
) AS cpu_seconds
FROM dba_hist_sqlstat s
WHERE s.snap_id BETWEEN
<START_SNAP_ID>
AND
<END_SNAP_ID>
GROUP BY s.sql_id
ORDER BY
SUM(s.direct_writes_delta) DESC
)
WHERE ROWNUM <= 30;
This does not prove that a SQL statement consumed a specific number of gigabytes of TEMP, but it is useful supporting evidence.
19. Check the SQL Execution Plan for TEMP-Heavy Operations
Historical execution plans can also provide clues:
SELECT
sql_id,
plan_hash_value,
id,
operation,
options,
object_owner,
object_name,
temp_space
FROM dba_hist_sql_plan
WHERE sql_id = '<SQL_ID>'
ORDER BY plan_hash_value,
id;
Oracle 10g's historical SQL plan view includes the optimizer's estimated TEMP_SPACE requirement for operations such as sorts and hash joins.
Large values associated with operations such as:
HASH JOIN
SORT ORDER BY
SORT GROUP BY
WINDOW SORT
can support the diagnosis.
Remember that this is an optimizer estimate, not necessarily the exact amount actually consumed.
20. Oracle 19c – Check TEMP Free Space
Oracle 19c provides a simpler consolidated view:
SELECT
tablespace_name,
ROUND(
tablespace_size /
1024 / 1024 / 1024,
2
) AS total_gb,
ROUND(
allocated_space /
1024 / 1024 / 1024,
2
) AS allocated_gb,
ROUND(
free_space /
1024 / 1024 / 1024,
2
) AS free_gb
FROM dba_temp_free_space;
DBA_TEMP_FREE_SPACE provides allocated and free TEMP information and is useful alongside V$TEMP_SPACE_HEADER, V$SORT_SEGMENT and V$TEMPSEG_USAGE. Oracle's administration documentation lists these views specifically for temporary tablespace management.
21. Oracle 19c – Current TEMP Consumers
The basic query remains similar:
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.machine,
s.program,
s.module,
u.sql_id AS temp_sql_id,
s.sql_id AS current_sql_id,
u.segtype,
u.extents,
ROUND(
u.blocks * t.block_size /
1024 / 1024 / 1024,
3
) AS temp_used_gb,
u.con_id
FROM v$tempseg_usage u
JOIN v$session s
ON s.saddr = u.session_addr
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
ORDER BY temp_used_gb DESC;
For a multitenant database, investigate the appropriate PDB and pay attention to CON_ID.
22. Oracle 19c – Historical TEMP Usage by Session
Oracle 19c has an important advantage over 10g.
DBA_HIST_ACTIVE_SESS_HISTORY contains:
TEMP_SPACE_ALLOCATED
which records the TEMP allocation associated with a sampled session.
Example:
SELECT
ash.instance_number,
ash.session_id AS sid,
ash.session_serial# AS serial#,
u.username,
ash.sql_id,
ash.machine,
ash.program,
ash.module,
ROUND(
MAX(ash.temp_space_allocated) /
1024 / 1024 / 1024,
2
) AS peak_temp_gb,
COUNT(*) AS ash_samples,
TO_CHAR(
MIN(ash.sample_time),
'YYYY-MM-DD HH24:MI:SS'
) AS first_seen,
TO_CHAR(
MAX(ash.sample_time),
'YYYY-MM-DD HH24:MI:SS'
) AS last_seen
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_users u
ON u.user_id = ash.user_id
WHERE ash.sample_time >=
TO_TIMESTAMP(
'&start_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND ash.sample_time <
TO_TIMESTAMP(
'&end_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND ash.temp_space_allocated > 0
GROUP BY
ash.instance_number,
ash.session_id,
ash.session_serial#,
u.username,
ash.sql_id,
ash.machine,
ash.program,
ash.module
ORDER BY peak_temp_gb DESC;
Use:
MAX(temp_space_allocated)
rather than:
SUM(temp_space_allocated)
because ASH repeatedly samples the same active session. Summing the samples would repeatedly count the same allocation and could drastically exaggerate TEMP consumption.
23. Remediation Option 1 – Stop a Runaway Session
If a currently executing session is consuming excessive TEMP and the application owner confirms it can be terminated:
ALTER SYSTEM KILL SESSION
'<SID>,<SERIAL#>' IMMEDIATE;
Before doing this, identify:
SID
SERIAL#
USERNAME
MACHINE
PROGRAM
MODULE
SQL_ID
TEMP usage
Killing a session can terminate application work, roll back transactions and cause application errors, so it should be an operational decision rather than an automatic response.
Also remember:
Killing the session can release TEMP for reuse inside Oracle, but it does not automatically shrink the physical tempfile on disk.
24. Remediation Option 2 – Add TEMP Capacity on Another Filesystem
If the existing TEMP filesystem is already 100% full but another approved filesystem has capacity, an emergency option is to add another tempfile:
ALTER TABLESPACE TEMP
ADD TEMPFILE '<new_path>/temp03.dbf'
SIZE 5G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 20G;
This is generally safer than trying to manipulate an existing tempfile while critical SQL is still running.
Once the immediate capacity issue has been resolved, investigate the workload and reclaim unnecessary space from the original filesystem.
25. Remediation Option 3 – Resize a Tempfile in Oracle 10g
Oracle 10g supports resizing a tempfile with:
ALTER DATABASE TEMPFILE
'<tempfile_path>'
RESIZE 8G;
Oracle 10g's SQL syntax explicitly supports TEMPFILE ... RESIZE.
Do this only after checking:
V$TEMPSEG_USAGE
V$SORT_SEGMENT
active sessions
current TEMP requirements
Oracle will reject the resize if it cannot safely reduce the file to the requested size.
A sensible approach is to resize one tempfile first, verify filesystem recovery and database behaviour, and then decide whether additional reduction is appropriate.
Afterward:
SELECT
file_id,
file_name,
ROUND(bytes / 1024 / 1024 / 1024, 2)
AS size_gb
FROM dba_temp_files
ORDER BY file_id;
and:
df -h <temp_mount_point>
26. Remediation Option 4 – Replace an Oversized TEMP Tablespace in Oracle 10g
If resizing cannot reclaim enough space, a controlled maintenance approach is to create a replacement temporary tablespace on a filesystem with sufficient capacity.
Example:
CREATE TEMPORARY TABLESPACE TEMP_NEW
TEMPFILE '<new_path>/temp_new01.dbf'
SIZE 10G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 30G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;
Switch the database default:
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
Verify user assignments:
SELECT
username,
temporary_tablespace
FROM dba_users
ORDER BY temporary_tablespace,
username;
Where necessary:
ALTER USER <username>
TEMPORARY TABLESPACE TEMP_NEW;
After confirming that the old temporary tablespace is no longer required and no sessions depend on it, it can be removed during an appropriate maintenance procedure.
Do not rush this step simply because the filesystem is full.
27. Remediation Option 5 – Oracle 19c SHRINK SPACE
Oracle 19c provides significantly better TEMP shrink functionality.
To shrink the entire temporary tablespace while retaining a minimum size:
ALTER TABLESPACE TEMP
SHRINK SPACE KEEP 20G;
To shrink an individual tempfile:
ALTER TABLESPACE TEMP
SHRINK TEMPFILE
'<tempfile_path>'
KEEP 10G;
Oracle documents SHRINK SPACE and SHRINK TEMPFILE specifically for temporary tablespaces. The operation is online: user sessions can continue allocating sort extents and existing queries are not disrupted simply because the shrink is being performed.
The KEEP value represents a lower bound, not a guarantee that Oracle will reduce the tempfile to exactly that value.
28. Remediation Option 6 – Correct AUTOEXTEND and MAXSIZE
After the incident, review every tempfile:
SELECT
file_name,
ROUND(bytes / 1024 / 1024 / 1024, 2)
AS current_gb,
autoextensible,
ROUND(maxbytes / 1024 / 1024 / 1024, 2)
AS max_gb
FROM dba_temp_files;
A dangerous configuration might allow multiple tempfiles to independently expand to values that collectively exceed the filesystem capacity.
Set a controlled maximum:
ALTER DATABASE TEMPFILE
'<tempfile_path>'
AUTOEXTEND ON
NEXT 512M
MAXSIZE 20G;
The correct size depends on:
historical peak TEMP usage
normal workload
batch/reporting requirements
concurrency
filesystem capacity
growth margin
Do not simply set:
MAXSIZE UNLIMITED
without considering the filesystem boundary.
29. Does Restarting Oracle Fix the Problem?
Restarting Oracle may clear the in-memory TEMP sort-segment state and terminate sessions that are holding temporary allocations.
However, restarting the database does not normally reduce the physical tempfile sizes.
For example:
Before restart:
TEMP01 = 18 GB
TEMP02 = 14 GB
After restart:
TEMP01 = 18 GB
TEMP02 = 14 GB
The files still occupy approximately 32 GB of filesystem capacity.
Therefore, restarting Oracle may change internal TEMP allocation statistics, but it is not a substitute for resizing, shrinking or correctly restructuring the TEMP tablespace when the actual problem is filesystem capacity.
30. Recommended Troubleshooting Sequence
When the mount point hosting TEMP reaches 100%, use the following order:
Confirm the affected filesystem with
df -h.Identify all temporary tablespaces.
Check every tempfile's physical size, autoextend and maximum size.
Check
V$TEMP_SPACE_HEADER.Check
V$SORT_SEGMENTto distinguish active extents from reusable extents.Check
V$TEMPSEG_USAGEto find actual current consumers.Identify username, machine, program, module and SQL ID.
Check all currently active sessions.
Determine TEMP segment types such as
SORT,HASHandLOB_DATA.Retrieve the SQL text.
If the incident has already passed, identify when TEMP grew using AWR, if licensed.
Correlate that growth period with ASH sessions, machines, programs and SQL IDs.
On Oracle 10g, use sort/direct-write activity as supporting evidence because historical ASH does not provide per-session
TEMP_SPACE_ALLOCATED.On Oracle 19c, use historical
TEMP_SPACE_ALLOCATEDto identify peak sampled TEMP consumers.Remediate the immediate capacity issue.
Reconfigure autoextend and
MAXSIZE.Analyse and tune the SQL or application behaviour that caused the original growth.
Oracle 10g Quick Diagnostic Script
The following compact set provides a useful first response.
Tempfile size
SELECT
file_id,
file_name,
ROUND(bytes / 1024 / 1024 / 1024, 2)
AS size_gb,
autoextensible,
ROUND(maxbytes / 1024 / 1024 / 1024, 2)
AS max_gb
FROM dba_temp_files
ORDER BY file_id;
TEMP space header
SELECT
tablespace_name,
ROUND(SUM(bytes_used) / 1024 / 1024 / 1024, 2)
AS used_gb,
ROUND(SUM(bytes_free) / 1024 / 1024 / 1024, 2)
AS free_gb
FROM v$temp_space_header
GROUP BY tablespace_name;
Reusable extents
SELECT
s.tablespace_name,
s.current_users,
s.used_extents,
s.free_extents,
ROUND(
s.used_blocks * t.block_size /
1024 / 1024 / 1024,
2
) AS active_used_gb,
ROUND(
s.free_blocks * t.block_size /
1024 / 1024 / 1024,
2
) AS reusable_free_gb
FROM v$sort_segment s
JOIN dba_tablespaces t
ON t.tablespace_name = s.tablespace_name;
Actual current TEMP consumers
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.machine,
s.program,
s.module,
u.sql_id,
u.segtype,
ROUND(
u.blocks * t.block_size /
1024 / 1024 / 1024,
3
) AS temp_used_gb
FROM v$tempseg_usage u
JOIN v$session s
ON s.saddr = u.session_addr
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
ORDER BY temp_used_gb DESC;
Oracle 19c Quick Diagnostic Script
TEMP capacity
SELECT
tablespace_name,
ROUND(tablespace_size / 1024 / 1024 / 1024, 2)
AS total_gb,
ROUND(allocated_space / 1024 / 1024 / 1024, 2)
AS allocated_gb,
ROUND(free_space / 1024 / 1024 / 1024, 2)
AS free_gb
FROM dba_temp_free_space;
Current TEMP consumers
SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.module,
u.sql_id,
u.segtype,
ROUND(
u.blocks * t.block_size /
1024 / 1024 / 1024,
3
) AS temp_used_gb,
u.con_id
FROM v$tempseg_usage u
JOIN v$session s
ON s.saddr = u.session_addr
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
ORDER BY temp_used_gb DESC;
Historical peak TEMP consumers
SELECT
ash.session_id AS sid,
ash.session_serial# AS serial#,
u.username,
ash.sql_id,
ash.machine,
ash.program,
ash.module,
ROUND(
MAX(ash.temp_space_allocated) /
1024 / 1024 / 1024,
2
) AS peak_temp_gb
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_users u
ON u.user_id = ash.user_id
WHERE ash.sample_time >=
TO_TIMESTAMP(
'&start_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND ash.sample_time <
TO_TIMESTAMP(
'&end_time',
'YYYY-MM-DD HH24:MI:SS'
)
AND ash.temp_space_allocated > 0
GROUP BY
ash.session_id,
ash.session_serial#,
u.username,
ash.sql_id,
ash.machine,
ash.program,
ash.module
ORDER BY peak_temp_gb DESC;
Final Thoughts
When an Oracle TEMP filesystem reaches 100%, adding more disk space should not automatically be the first or only response.
The important distinction is between:
physical tempfile size
and:
currently active TEMP consumption
A tempfile can remain physically large long after the SQL that caused its growth has finished.
For Oracle 10g, the combination of:
DBA_TEMP_FILES
V$TEMP_SPACE_HEADER
V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$SESSION
provides the best current-state diagnosis.
For historical investigation, licensed AWR/ASH information can then be used to correlate tablespace growth with usernames, machines, application programs and SQL statements.
Oracle 19c improves the process further with DBA_TEMP_FREE_SPACE, historical TEMP_SPACE_ALLOCATED, and online SHRINK SPACE / SHRINK TEMPFILE functionality.
The safest long-term solution is not simply to make TEMP larger. It is to determine why it grew, identify the workload responsible, correctly size the TEMP tablespace, establish safe autoextend boundaries, and monitor filesystem capacity so that one temporary workload cannot consume the entire mount point.