ORA-01652 Every Monday at 9:05 AM for Seven Weeks

Every Monday at 9:05 AM, the finance team's weekly reconciliation report failed with ORA-01652: unable to extend temp segment. The DBA's response for seven weeks: add 20GB to the temp tablespace and re-run. By week seven, the temp tablespace was 200GB on a 500GB volume and still filling. Something was fundamentally wrong with how the report used temporary space.

The Alert

SELECT
  s.sid, s.serial#, s.username, s.program,
  q.sql_id,
  ROUND(u.blocks * 8 / 1024) AS temp_mb,
  u.tablespace
FROM v$sort_usage u
JOIN v$session s ON s.saddr = u.session_addr
LEFT JOIN v$sql q ON q.sql_id = s.sql_id
ORDER BY u.blocks DESC;

Not one session — 64 sessions. All from the same username, same program, same sql_id. Each using 2–3GB of temp. Total: 180GB. We checked three times over two minutes: 64 sessions each time, temp usage climbing. 64 parallel query slaves, each claiming its own temp segment for a hash join.

First Hypothesis: Temp Tablespace Needs More Autoextend

Seven weeks of adding space suggested the data volume was simply growing. We checked how much data the report actually processed.

The report joined three tables: 40M rows, 12M rows, 8M rows. Same table sizes for the last year — no growth. Ran the row counts three times on three different days. Identical. The data had not grown. The temp consumption was not proportional to data volume — it was proportional to parallelism.

The Discovery

SELECT
  table_name, degree, instances
FROM dba_tables
WHERE table_name IN ('JOURNAL_ENTRIES', 'GL_ACCOUNTS', 'PERIOD_CLOSE');

-- Check actual DOP of running parallel queries
SELECT
  qc_session_id, COUNT(*) AS slave_count
FROM v$px_session
GROUP BY qc_session_id;

The DEGREE column in dba_tables is a VARCHAR2, not a number. When it reads DEFAULT, it does not mean "use some reasonable default" — it means "use the CPU_COUNT parameter value as the degree of parallelism." On RAC, CPU_COUNT reflects the node's total CPUs. Moving the same application from a 16-CPU development server to a 64-CPU production RAC node quadruples parallelism without any configuration change. This is one of the most common parallel query traps in Oracle environments.

JOURNAL_ENTRIES: DEGREE = DEFAULT. On a 64-CPU RAC node, DEFAULT means 64 parallel slaves. The hash join between JOURNAL_ENTRIES and GL_ACCOUNTS was distributed across 64 slaves, each building its own hash table in temp. Verified three times: the DEGREE column showed DEFAULT, the px_session query showed exactly 64 slaves, and V$SQL_PLAN showed a HASH JOIN BUFFERED operation — the type that requires each slave to materialize its partition to temp.

Incident Timeline

Root Cause

DEGREE DEFAULT on a 64-CPU machine meant 64-way parallelism. The hash join's BUFFERED distribution forced each slave to build a complete hash table in temp. 64 slaves × 3GB each = 192GB of temp for a join that needed 3GB at serial execution. The weekly 20GB additions were always less than the 192GB peak — the report only succeeded because other sessions' temp was released between retries.

The Fix

-- Set explicit degree on the large table
ALTER TABLE journal_entries PARALLEL 4;

-- Or use a hint in the query
SELECT /*+ PARALLEL(j 4) */ ...
FROM journal_entries j ...;

-- Reclaim the over-allocated temp space
ALTER TABLESPACE temp SHRINK SPACE;

-- Verify current temp usage
SELECT tablespace_name,
  ROUND(tablespace_size / 1024 / 1024) AS total_mb,
  ROUND(allocated_space / 1024 / 1024) AS used_mb
FROM dba_temp_free_space;

Prevention

All tables now have explicit DEGREE settings — no DEFAULT anywhere. DEFAULT is dangerous because it ties parallelism to the CPU count, which changes when you move to a larger server or add RAC nodes. A table set to DEGREE DEFAULT on a 16-CPU test server runs with DOP 16; the same table on a 64-CPU production RAC node runs with DOP 64. The difference in temp consumption is 4x — enough to turn a query that fits in 40GB into one that needs 160GB.

A Resource Manager plan now caps parallel DOP at 8 for the reporting consumer group. Temp tablespace has a hard MAXSIZE of 40GB — if a query needs more, it fails immediately rather than filling the volume. Temp usage monitoring alerts at 70%. We also added a pre-execution check to the reporting framework that estimates temp consumption using EXPLAIN PLAN and the expected DOP: if estimated temp exceeds 30GB, the query is rejected with a message directing the user to the DBA team for optimization.

The seven weeks of incrementally adding temp space was the real failure. Each time the DBA added 20GB, the symptom disappeared until the following Monday. The fix-the-symptom pattern prevented root cause investigation. We added a post-incident rule: if a space-related error recurs within 30 days, the second occurrence triggers a mandatory root cause analysis before any additional space is allocated. Adding space is not a fix — it is buying time, and that time must be used for investigation. — no DEFAULT anywhere. A Resource Manager plan caps parallel DOP at 8 for the reporting consumer group. Temp tablespace has a hard MAXSIZE of 40GB — if a query needs more, it fails immediately rather than filling the volume. Temp usage monitoring alerts at 70%.