ORA-01652 Unable to Extend Temp Segment - Temporary Tablespace Management

ORA-01652: Unable to Extend Temp Segment

Error Overview

Error Text: ORA-01652: unable to extend temp segment by [blocks] in tablespace [tablespace_name]

This error occurs when Oracle cannot allocate space for temporary segments used in sorting operations, hash joins, index creation, or other operations requiring temporary space. It indicates that the temporary tablespace is full and cannot extend to accommodate the space requirement.

Understanding Temporary Space Usage

Temporary Space Operations

SQL Operation Requiring Sort/Hash
       ↓
Request Temporary Space
       ↓
Allocate Temp Segment in TEMP Tablespace
       ↓
Check Available Space
├── Free Space Available → Allocate Segment ✓
└── No Free Space → ORA-01652 Error ✗

Operations That Use Temporary Space

• Sorting operations - ORDER BY, GROUP BY, DISTINCT
• Hash joins - Large table joins
• Index creation - CREATE INDEX, REBUILD INDEX
• Analytics functions - Window functions, statistical operations
• Temporary tables - Global temporary table operations
• Export/Import - Data Pump operations

Diagnostic Steps

1. Check Temporary Tablespace Usage

-- Check temporary tablespace space usage
SELECT
  tablespace_name,
  ROUND(total_mb, 2) as total_mb,
  ROUND(used_mb, 2) as used_mb,
  ROUND(free_mb, 2) as free_mb,
  ROUND(used_pct, 2) as used_pct,
  autoextensible
FROM (
  SELECT
    tf.tablespace_name,
    SUM(tf.bytes)/1024/1024 as total_mb,
    NVL(SUM(tu.bytes_used)/1024/1024, 0) as used_mb,
    SUM(tf.bytes)/1024/1024 - NVL(SUM(tu.bytes_used)/1024/1024, 0) as free_mb,
    NVL(SUM(tu.bytes_used)/SUM(tf.bytes) * 100, 0) as used_pct,
    CASE WHEN COUNT(CASE WHEN tf.autoextensible = 'YES' THEN 1 END) > 0
         THEN 'YES' ELSE 'NO' END as autoextensible
  FROM dba_temp_files tf
  LEFT JOIN v$temp_space_header tu ON tf.tablespace_name = tu.tablespace_name
  GROUP BY tf.tablespace_name
)
ORDER BY used_pct DESC;

-- Check current temporary space usage by session
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  s.sql_id,
  ROUND(tu.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2) as temp_mb
FROM v$session s, v$tempseg_usage tu
WHERE s.saddr = tu.session_addr
ORDER BY tu.blocks DESC;

-- Check temporary tablespace file configuration
SELECT
  file_name,
  tablespace_name,
  ROUND(bytes/1024/1024, 2) as size_mb,
  ROUND(maxbytes/1024/1024, 2) as max_size_mb,
  autoextensible,
  increment_by * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024 as increment_mb
FROM dba_temp_files
ORDER BY tablespace_name, file_id;

2. Identify Memory-Intensive Operations

-- Find sessions using most temporary space
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.sql_id,
  st.sql_text,
  ROUND(tu.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2) as temp_mb,
  s.status,
  s.last_call_et
FROM v$session s, v$tempseg_usage tu, v$sqltext st
WHERE s.saddr = tu.session_addr
  AND s.sql_address = st.address(+)
  AND s.sql_hash_value = st.hash_value(+)
  AND st.piece = 0
ORDER BY tu.blocks DESC;

-- Check sort area usage
SELECT
  sid,
  serial#,
  username,
  operation_type,
  ROUND(work_area_size/1024/1024, 2) as workarea_mb,
  ROUND(expected_size/1024/1024, 2) as expected_mb,
  ROUND(actual_mem_used/1024/1024, 2) as actual_mb,
  ROUND(max_mem_used/1024/1024, 2) as max_mb,
  number_passes,
  tempseg_size/1024/1024 as tempseg_mb
FROM v$sql_workarea_active
WHERE tempseg_size > 0
ORDER BY tempseg_size DESC;

-- Check PGA and sort area configuration
SHOW PARAMETER sort_area_size;
SHOW PARAMETER hash_area_size;
SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER workarea_size_policy;

3. Analyze SQL Causing the Error

-- Find SQL statements currently using temporary space
SELECT
  sql_id,
  child_number,
  sql_text,
  sorts,
  rows_processed,
  executions,
  ROUND(elapsed_time/1000000, 2) as elapsed_seconds,
  ROUND(cpu_time/1000000, 2) as cpu_seconds
FROM v$sql
WHERE sql_id IN (
  SELECT DISTINCT sql_id
  FROM v$session
  WHERE saddr IN (SELECT session_addr FROM v$tempseg_usage)
)
ORDER BY sorts DESC;

-- Check execution plans for sort operations
SELECT
  sql_id,
  operation,
  options,
  object_name,
  cost,
  bytes,
  temp_space
FROM v$sql_plan
WHERE sql_id = 'SQL_ID_FROM_ABOVE'  -- Replace with actual SQL_ID
  AND (operation LIKE '%SORT%' OR operation LIKE '%HASH%')
ORDER BY id;

Immediate Solutions

Solution 1: Add More Temporary Space

Add Temporary Files

-- Add new temporary file to temporary tablespace
ALTER TABLESPACE temp
ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf'
SIZE 2G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;

-- For Oracle Managed Files (OMF)
ALTER TABLESPACE temp
ADD TEMPFILE
SIZE 2G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;

-- Verify the new tempfile
SELECT
  file_name,
  ROUND(bytes/1024/1024, 2) as size_mb,
  autoextensible,
  ROUND(maxbytes/1024/1024, 2) as max_size_mb
FROM dba_temp_files
WHERE tablespace_name = 'TEMP'
ORDER BY file_id;

Resize Existing Temporary Files

-- Check current temporary file sizes
SELECT
  file_name,
  ROUND(bytes/1024/1024, 2) as current_size_mb,
  ROUND(maxbytes/1024/1024, 2) as max_size_mb,
  autoextensible
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';

-- Resize temporary file
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
RESIZE 5G;

-- Enable autoextend if not already enabled
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 50G;

Solution 2: Optimize Query Performance

Kill Long-Running Sessions Using Excessive Temp Space

-- Identify sessions to potentially kill
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  s.sql_id,
  ROUND(tu.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2) as temp_mb,
  ROUND(s.last_call_et/3600, 2) as hours_active
FROM v$session s, v$tempseg_usage tu
WHERE s.saddr = tu.session_addr
  AND tu.blocks > 131072  -- More than 1GB temp space
ORDER BY tu.blocks DESC;

-- Kill specific session (be very careful!)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Cancel long-running SQL operation
ALTER SYSTEM CANCEL SQL 'sid,serial#,sql_id';

Optimize SQL Queries

-- Analyze problematic SQL for optimization opportunities
-- Example: Rewrite queries to use less temporary space

-- BAD: Large Cartesian product requiring huge temp space
SELECT t1.*, t2.*
FROM large_table1 t1, large_table2 t2
WHERE t1.status = 'ACTIVE'
  AND t2.status = 'ACTIVE'
ORDER BY t1.date_created, t2.date_created;

-- GOOD: Add proper join condition
SELECT t1.*, t2.*
FROM large_table1 t1
JOIN large_table2 t2 ON t1.id = t2.ref_id
WHERE t1.status = 'ACTIVE'
  AND t2.status = 'ACTIVE'
ORDER BY t1.date_created, t2.date_created;

-- BAD: Unnecessary sorting of large result set
SELECT * FROM (
  SELECT * FROM large_table
  ORDER BY date_column
)
WHERE rownum <= 100;

-- GOOD: Limit before sorting
SELECT * FROM (
  SELECT * FROM large_table
  WHERE date_column >= SYSDATE - 30  -- Limit data first
  ORDER BY date_column
)
WHERE rownum <= 100;

Solution 3: Adjust Memory Parameters

Optimize PGA and Sort Parameters

-- Check current memory configuration
SELECT name, value, description
FROM v$parameter
WHERE name IN (
  'pga_aggregate_target',
  'workarea_size_policy',
  'sort_area_size',
  'hash_area_size'
);

-- Increase PGA target to reduce temp space usage
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;

-- For manual workarea management
ALTER SYSTEM SET workarea_size_policy = MANUAL SCOPE=BOTH;
ALTER SYSTEM SET sort_area_size = 104857600 SCOPE=BOTH;  -- 100MB
ALTER SYSTEM SET hash_area_size = 104857600 SCOPE=BOTH;  -- 100MB

-- Return to automatic management (recommended)
ALTER SYSTEM SET workarea_size_policy = AUTO SCOPE=BOTH;

-- Check workarea advisor recommendations
SELECT
  pga_target_for_estimate/1024/1024/1024 as pga_target_gb,
  pga_target_factor,
  estd_extra_bytes_rw/1024/1024/1024 as estd_extra_gb,
  estd_pga_cache_hit_percentage,
  estd_overallocation_count
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

Long-Term Solutions

1. Create Additional Temporary Tablespaces

Multiple Temporary Tablespaces Strategy

-- Create additional temporary tablespaces
CREATE TEMPORARY TABLESPACE temp_large
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_large01.dbf' SIZE 10G,
         '/u01/app/oracle/oradata/orcl/temp_large02.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE 50G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

-- Create temporary tablespace for specific operations
CREATE TEMPORARY TABLESPACE temp_dw
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_dw01.dbf' SIZE 20G
AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

-- Assign users to specific temporary tablespaces
ALTER USER batch_user TEMPORARY TABLESPACE temp_large;
ALTER USER etl_user TEMPORARY TABLESPACE temp_dw;
ALTER USER app_user TEMPORARY TABLESPACE temp;

-- Create temporary tablespace group
CREATE TEMPORARY TABLESPACE temp_group1
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_grp1_01.dbf' SIZE 5G;

CREATE TEMPORARY TABLESPACE temp_group2
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_grp2_01.dbf' SIZE 5G;

-- Add tablespaces to group
ALTER TABLESPACE temp_group1 TABLESPACE GROUP temp_tg;
ALTER TABLESPACE temp_group2 TABLESPACE GROUP temp_tg;

-- Assign users to tablespace group
ALTER USER heavy_user TEMPORARY TABLESPACE temp_tg;

2. Implement Temporary Space Monitoring

Comprehensive Monitoring System

-- Create temporary space monitoring table
CREATE TABLE temp_space_alerts (
  alert_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  tablespace_name VARCHAR2(30),
  used_mb NUMBER,
  total_mb NUMBER,
  used_pct NUMBER,
  alert_level VARCHAR2(20),
  top_session_sid NUMBER,
  top_session_temp_mb NUMBER,
  action_taken VARCHAR2(500)
);

-- Monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_temp_space AS
  CURSOR temp_ts_cursor IS
    SELECT
      tf.tablespace_name,
      SUM(tf.bytes)/1024/1024 as total_mb,
      NVL(SUM(tu.bytes_used)/1024/1024, 0) as used_mb,
      NVL(SUM(tu.bytes_used)/SUM(tf.bytes) * 100, 0) as used_pct
    FROM dba_temp_files tf
    LEFT JOIN v$temp_space_header tu ON tf.tablespace_name = tu.tablespace_name
    GROUP BY tf.tablespace_name;

  v_alert_level VARCHAR2(20);
  v_top_session_sid NUMBER;
  v_top_session_temp_mb NUMBER;
BEGIN
  FOR temp_rec IN temp_ts_cursor LOOP
    -- Determine alert level
    IF temp_rec.used_pct >= 95 THEN
      v_alert_level := 'CRITICAL';
    ELSIF temp_rec.used_pct >= 85 THEN
      v_alert_level := 'WARNING';
    ELSIF temp_rec.used_pct >= 75 THEN
      v_alert_level := 'INFO';
    ELSE
      v_alert_level := 'OK';
    END IF;

    -- Find top session using temp space
    BEGIN
      SELECT s.sid, ROUND(tu.blocks * 8192 / 1024 / 1024, 2)
      INTO v_top_session_sid, v_top_session_temp_mb
      FROM v$session s, v$tempseg_usage tu
      WHERE s.saddr = tu.session_addr
        AND tu.tablespace = temp_rec.tablespace_name
        AND ROWNUM = 1
      ORDER BY tu.blocks DESC;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_top_session_sid := NULL;
        v_top_session_temp_mb := 0;
    END;

    -- Log alerts for non-OK conditions
    IF v_alert_level != 'OK' THEN
      INSERT INTO temp_space_alerts (
        tablespace_name, used_mb, total_mb, used_pct, alert_level,
        top_session_sid, top_session_temp_mb
      ) VALUES (
        temp_rec.tablespace_name, temp_rec.used_mb, temp_rec.total_mb,
        temp_rec.used_pct, v_alert_level, v_top_session_sid, v_top_session_temp_mb
      );

      -- Auto-remediation for critical situations
      IF v_alert_level = 'CRITICAL' THEN
        BEGIN
          -- Add temporary file
          EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || temp_rec.tablespace_name ||
                          ' ADD TEMPFILE SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 20G';

          UPDATE temp_space_alerts
          SET action_taken = 'Added 2GB tempfile automatically'
          WHERE tablespace_name = temp_rec.tablespace_name
            AND alert_time = (SELECT MAX(alert_time) FROM temp_space_alerts
                             WHERE tablespace_name = temp_rec.tablespace_name);
        EXCEPTION
          WHEN OTHERS THEN
            UPDATE temp_space_alerts
            SET action_taken = 'Failed to add tempfile: ' || SQLERRM
            WHERE tablespace_name = temp_rec.tablespace_name
              AND alert_time = (SELECT MAX(alert_time) FROM temp_space_alerts
                               WHERE tablespace_name = temp_rec.tablespace_name);
        END;
      END IF;
    END IF;
  END LOOP;

  COMMIT;
END;
/

-- Schedule monitoring job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_TEMP_SPACE',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_temp_space',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
    enabled => TRUE
  );
END;
/

3. Query Optimization Framework

Automatic SQL Tuning for Temp Space Issues

-- Create procedure to identify and tune problematic SQL
CREATE OR REPLACE PROCEDURE tune_temp_intensive_sql AS
  CURSOR sql_cursor IS
    SELECT DISTINCT sql_id, child_number
    FROM v$sql_workarea
    WHERE operation_type IN ('SORT', 'HASH-JOIN')
      AND max_tempseg_size > 1073741824  -- > 1GB temp usage
      AND executions > 1;

  v_task_name VARCHAR2(30);
  v_tuning_task VARCHAR2(30);
BEGIN
  FOR sql_rec IN sql_cursor LOOP
    v_task_name := 'TUNE_TEMP_' || sql_rec.sql_id;

    -- Create SQL tuning task
    v_tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
      sql_id => sql_rec.sql_id,
      child_number => sql_rec.child_number,
      task_name => v_task_name,
      description => 'Tuning for excessive temp space usage'
    );

    -- Execute tuning task
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_tuning_task);

    -- Log the task creation
    INSERT INTO sql_tuning_log (
      log_date, sql_id, task_name, status
    ) VALUES (
      SYSDATE, sql_rec.sql_id, v_task_name, 'CREATED'
    );
  END LOOP;

  COMMIT;
END;
/

-- Create tuning log table
CREATE TABLE sql_tuning_log (
  log_date DATE,
  sql_id VARCHAR2(13),
  task_name VARCHAR2(30),
  status VARCHAR2(20),
  recommendations CLOB
);

-- View tuning recommendations
SELECT
  task_name,
  DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name) as recommendations
FROM user_advisor_tasks
WHERE task_name LIKE 'TUNE_TEMP_%';

Specialized Solutions

1. Index Creation and Rebuilds

-- For index operations causing temp space issues
-- Use NOLOGGING and parallel processing
CREATE INDEX schema.large_index ON schema.large_table(column1, column2)
TABLESPACE indexes
NOLOGGING
PARALLEL 4
COMPUTE STATISTICS;

-- Re-enable logging after creation
ALTER INDEX schema.large_index LOGGING;
ALTER INDEX schema.large_index NOPARALLEL;

-- For index rebuilds, use online option when possible
ALTER INDEX schema.large_index REBUILD ONLINE
TABLESPACE indexes
PARALLEL 4;

2. Data Loading Operations

-- Optimize data loading to reduce temp space usage
-- Use direct path loading
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table
WHERE condition;

-- Use parallel processing for large operations
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(target_table, 4) */ INTO target_table
SELECT /*+ PARALLEL(source_table, 4) */ * FROM source_table;

-- Disable parallel after operation
ALTER SESSION DISABLE PARALLEL DML;

3. Analytics and Reporting

-- For large analytical queries, consider partitioning approaches
-- Instead of sorting entire result set
SELECT customer_id, SUM(amount), ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) as rank
FROM sales_data
GROUP BY customer_id;

-- Process in chunks
SELECT customer_id, total_amount, rank
FROM (
  SELECT customer_id, SUM(amount) as total_amount
  FROM sales_data
  WHERE sale_date >= SYSDATE - 30  -- Limit time range
  GROUP BY customer_id
)
ORDER BY total_amount DESC
FETCH FIRST 1000 ROWS ONLY;

Related Errors

• ORA-01653 - Unable to extend table
• ORA-01654 - Unable to extend index
• ORA-04031 - Unable to allocate shared memory
• ORA-04030 - Out of process memory

Quick Reference

Emergency Response Steps

1. ✓ Check temporary tablespace usage immediately
2. ✓ Identify sessions using most temporary space
3. ✓ Add temporary files or resize existing ones
4. ✓ Consider killing long-running operations if critical
5. ✓ Optimize SQL causing excessive temp usage
6. ✓ Implement monitoring to prevent recurrence

Quick Commands

-- Check temp tablespace usage
SELECT tablespace_name, ROUND(used_percent, 2) as used_pct
FROM dba_tablespace_usage_metrics
WHERE contents = 'TEMPORARY'
ORDER BY used_percent DESC;

-- Add temporary file
ALTER TABLESPACE temp ADD TEMPFILE SIZE 2G AUTOEXTEND ON;

-- Find sessions using temp space
SELECT s.sid, s.username, ROUND(tu.blocks*8192/1024/1024, 2) as temp_mb
FROM v$session s, v$tempseg_usage tu
WHERE s.saddr = tu.session_addr
ORDER BY tu.blocks DESC;

-- Kill session using excessive temp space
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Sizing Guidelines

• OLTP systems: Temp tablespace = 10-20% of data size
• Data warehouse: Temp tablespace = 50-100% of largest table
• Mixed workload: Temp tablespace = 25-50% of data size
• Batch processing: May need temp space = 2x largest operation