Oracle memory and TEMP issues can look like a single resource shortage, but the causes differ. A large sort or hash join may spill because of plan shape, row estimates, concurrent workarea demand or an appropriately constrained memory target. TEMP growth may be an expected batch characteristic, a missing index symptom or a sign that one query is producing an unexpectedly large intermediate result.
Find the statements behind the allocation
Start with the incident interval and rank SQL by elapsed time, reads, temporary work and execution count. Inspect the plan for large sorts, hash operations, Cartesian joins and inaccurate cardinality. Compare active-session demand with memory and TEMP trends. One expensive query may be manageable alone but problematic when many copies run concurrently.
Separate capacity from query design
- Check whether filters and joins reduce rows early enough.
- Validate statistics and bind-sensitive plan behaviour.
- Identify reports or batch jobs that overlap with critical traffic.
- Measure TEMP usage per workload rather than only total tablespace size.
- Review memory configuration as part of a complete concurrency model.
Do not simply enlarge TEMP
More TEMP can prevent an immediate allocation failure, but it does not make an inefficient operation cheap. Likewise, more memory may improve one sort while allowing too much concurrent demand. Make a targeted SQL, schedule or capacity change, then compare elapsed time, TEMP allocation, CPU and I/O under a representative load.
Treat TEMP as an operational capacity with a workload owner, growth forecast and alerting policy, not as an unexplained emergency disk pool.
Separate allocated space from current consumers
A large temporary tablespace file does not mean every byte is actively used by a query. Examine current temporary-segment use through V$TEMPSEG_USAGE and workarea behaviour through permitted views such as V$SQL_WORKAREA_ACTIVE. Identify the SQL and sessions consuming resources while the failure occurs. An ORA-01652 report needs its tablespace and operation context, not an automatic assumption that every failure has the same cause.
SELECT sql_id, operation_type, actual_mem_used,
tempseg_size, number_passes
FROM v$sql_workarea_active
ORDER BY tempseg_size DESC NULLS LAST;
This Oracle 19c example is a live view. A completed statement may disappear before you connect. Keep the application job identifier and start time with the error record, and capture approved diagnostic snapshots during a controlled reproduction.
Example: a join multiplies an overnight report
An overnight report joins line items to a history table without restricting the current history version. Its intermediate row count grows dramatically, then a final sort consumes TEMP. Enlarging the tablespace may allow the report to finish but leaves the unnecessary work intact. Inspect join cardinality and business keys first; the right fix may be a corrected join rather than more memory.
Compare estimates and actual row counts where safely available. Reproduce with representative data volumes because a small development database may conceal the multiplication. Any execution-based plan capture actually runs work, so budget its runtime and use a controlled environment for expensive or mutating statements.
Treat emergency capacity and permanent remediation separately
An approved TEMP extension may protect an urgent business deadline while the query is corrected. Verify underlying disk capacity, autoextend limits and other consumers before relying on it. Document that this is a capacity intervention so a later reviewer does not mistake the larger limit for evidence that the SQL improved.
For the permanent fix, compare report duration, peak temporary use, workarea passes and concurrent transactional latency. Test the full overlapping schedule: one report may fit comfortably while several simultaneous copies do not. Keep an explicit workload cap or scheduling rule if peak concurrency is the real constraint. A successful run at half the normal data volume is not sufficient acceptance for month-end processing.
Keep the evidence for the next incident
Mini DBA Oracle monitoring combines query and metric history with sessions, waits and resource diagnostics for incident investigation. Available evidence depends on database permissions and collection configuration; operators must confirm diagnostic entitlements for their environment.
References and further reading