ORA-01688 Unable to Extend Table Partition - Partition Management

ORA-01688: Unable to Extend Table Partition

Error Overview

Error Text: ORA-01688: unable to extend table [schema].[table] partition [partition] by [blocks] in tablespace [tablespace_name]

This error occurs when Oracle cannot allocate additional space for a specific table partition because the tablespace containing that partition is full or cannot extend. This is particularly common in partitioned environments where different partitions may be stored in different tablespaces and grow at different rates.

Understanding Partitioned Table Storage

Partition Space Architecture

Partitioned Table
├── Partition P1 → Tablespace TS1 ← Full/Cannot Extend
├── Partition P2 → Tablespace TS2 ← OK
├── Partition P3 → Tablespace TS3 ← OK
└── Partition P4 → Tablespace TS4 ← OK

Common Partition Scenarios

• Monthly partitions - Current month partition grows rapidly
• Range partitions - Specific value ranges fill up faster
• List partitions - Certain categories have more data
• Hash partitions - Uneven data distribution

Diagnostic Steps

1. Identify the Problem Partition

-- Find the specific partition from error message
SELECT
  table_owner,
  table_name,
  partition_name,
  tablespace_name,
  high_value,
  num_rows,
  last_analyzed
FROM dba_tab_partitions
WHERE table_owner = 'SCHEMA_NAME'  -- From error message
  AND table_name = 'TABLE_NAME'    -- From error message
  AND partition_name = 'PARTITION_NAME';  -- From error message

-- Check partition storage details
SELECT
  tp.table_owner,
  tp.table_name,
  tp.partition_name,
  tp.tablespace_name,
  ds.bytes/1024/1024 as size_mb,
  ds.extents,
  ds.max_extents,
  ds.next_extent/1024/1024 as next_extent_mb
FROM dba_tab_partitions tp, dba_segments ds
WHERE tp.table_owner = ds.owner
  AND tp.table_name = ds.segment_name
  AND tp.partition_name = ds.partition_name
  AND tp.table_owner = 'SCHEMA_NAME'
  AND tp.table_name = 'TABLE_NAME'
  AND tp.partition_name = 'PARTITION_NAME';

2. Analyze Tablespace Usage for Partition

-- Check tablespace space usage for the specific partition
SELECT
  df.tablespace_name,
  ROUND(SUM(df.bytes)/1024/1024, 2) as total_mb,
  ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0))/1024/1024, 2) as used_mb,
  ROUND(NVL(SUM(fs.bytes)/1024/1024, 0), 2) as free_mb,
  ROUND(((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes)) * 100, 2) as used_pct,
  CASE WHEN COUNT(CASE WHEN df.autoextensible = 'YES' THEN 1 END) > 0
       THEN 'YES' ELSE 'NO' END as autoextensible
FROM dba_data_files df
LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
WHERE df.tablespace_name = (
  SELECT tablespace_name
  FROM dba_tab_partitions
  WHERE table_owner = 'SCHEMA_NAME'
    AND table_name = 'TABLE_NAME'
    AND partition_name = 'PARTITION_NAME'
)
GROUP BY df.tablespace_name;

-- Check all partitions and their tablespace usage
SELECT
  tp.partition_name,
  tp.tablespace_name,
  ROUND(ds.bytes/1024/1024, 2) as partition_size_mb,
  ROUND(tu.used_percent, 2) as tablespace_used_pct,
  tu.tablespace_name as ts_name
FROM dba_tab_partitions tp
LEFT JOIN dba_segments ds ON (tp.table_owner = ds.owner
                             AND tp.table_name = ds.segment_name
                             AND tp.partition_name = ds.partition_name)
LEFT JOIN dba_tablespace_usage_metrics tu ON tp.tablespace_name = tu.tablespace_name
WHERE tp.table_owner = 'SCHEMA_NAME'
  AND tp.table_name = 'TABLE_NAME'
ORDER BY tu.used_percent DESC, ds.bytes DESC;

3. Check Partition Growth Patterns

-- Analyze partition sizes and growth trends
SELECT
  partition_name,
  tablespace_name,
  num_rows,
  ROUND(avg_row_len) as avg_row_len,
  ROUND((num_rows * avg_row_len)/1024/1024, 2) as estimated_size_mb,
  last_analyzed,
  high_value
FROM dba_tab_partitions
WHERE table_owner = 'SCHEMA_NAME'
  AND table_name = 'TABLE_NAME'
ORDER BY partition_position DESC;

-- Check for active operations on partitions
SELECT
  sid,
  serial#,
  username,
  sql_id,
  operation,
  target,
  ROUND(elapsed_seconds/60, 2) as elapsed_minutes,
  ROUND(time_remaining/60, 2) as remaining_minutes,
  ROUND(sofar/totalwork*100, 2) as pct_complete
FROM v$session_longops
WHERE target LIKE '%' || 'TABLE_NAME' || '%'
   OR target LIKE '%' || 'PARTITION_NAME' || '%'
ORDER BY start_time DESC;

Immediate Solutions

Solution 1: Add Space to Partition Tablespace

Add Datafile to Partition Tablespace

-- Identify the tablespace for the failing partition
SELECT tablespace_name
FROM dba_tab_partitions
WHERE table_owner = 'SCHEMA_NAME'
  AND table_name = 'TABLE_NAME'
  AND partition_name = 'PARTITION_NAME';

-- Add datafile to the tablespace
ALTER TABLESPACE partition_ts_202312
ADD DATAFILE '/u01/app/oracle/oradata/orcl/partition_ts_202312_02.dbf'
SIZE 2G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;

-- For Oracle Managed Files
ALTER TABLESPACE partition_ts_202312
ADD DATAFILE
SIZE 2G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;

Resize Existing Datafiles

-- Check current datafile configuration
SELECT
  file_name,
  ROUND(bytes/1024/1024, 2) as size_mb,
  ROUND(maxbytes/1024/1024, 2) as max_size_mb,
  autoextensible
FROM dba_data_files
WHERE tablespace_name = 'PARTITION_TS_202312';

-- Resize datafile
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/partition_ts_202312_01.dbf'
RESIZE 5G;

-- Enable autoextend
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/partition_ts_202312_01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 50G;

Solution 2: Move Partition to Different Tablespace

Move Partition to Tablespace with More Space

-- Check available space in other tablespaces
SELECT
  tablespace_name,
  ROUND(SUM(bytes)/1024/1024, 2) as free_mb
FROM dba_free_space
WHERE tablespace_name IN ('DATA_2024', 'LARGE_DATA', 'ARCHIVE_DATA')
GROUP BY tablespace_name
ORDER BY free_mb DESC;

-- Move partition to different tablespace
ALTER TABLE schema.table_name
MOVE PARTITION partition_name TABLESPACE data_2024;

-- Rebuild local indexes after partition move
SELECT
  'ALTER INDEX ' || index_owner || '.' || index_name ||
  ' REBUILD PARTITION ' || partition_name || ';' as rebuild_cmd
FROM dba_ind_partitions
WHERE index_owner = 'SCHEMA_NAME'
  AND index_name IN (
    SELECT index_name FROM dba_indexes
    WHERE table_owner = 'SCHEMA_NAME'
      AND table_name = 'TABLE_NAME'
      AND locality = 'LOCAL'
  )
  AND partition_name = 'PARTITION_NAME';

-- Execute the rebuild commands
ALTER INDEX schema.idx_table_local REBUILD PARTITION partition_name;

Solution 3: Partition Maintenance Operations

Split Large Partition

-- For range partitions - split if partition is too large
-- First, check current partition boundaries
SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_owner = 'SCHEMA_NAME'
  AND table_name = 'TABLE_NAME'
ORDER BY partition_position;

-- Split partition at a specific value
ALTER TABLE schema.table_name
SPLIT PARTITION partition_name AT (DATE '2023-12-15')
INTO (
  PARTITION partition_name_part1 TABLESPACE data_2023,
  PARTITION partition_name_part2 TABLESPACE data_2024
);

-- For list partitions
ALTER TABLE schema.table_name
SPLIT PARTITION partition_name VALUES ('VALUE1', 'VALUE2')
INTO (
  PARTITION partition_name_subset TABLESPACE data_subset,
  PARTITION partition_name TABLESPACE data_remaining
);

Create New Partitions for Future Data

-- Add new partition for upcoming period
ALTER TABLE schema.table_name
ADD PARTITION p_2024_01
VALUES LESS THAN (DATE '2024-02-01')
TABLESPACE data_2024_01;

-- For list partitions
ALTER TABLE schema.table_name
ADD PARTITION p_new_category
VALUES ('NEW_VALUE1', 'NEW_VALUE2')
TABLESPACE data_new;

-- For hash partitions (adds and redistributes)
ALTER TABLE schema.table_name
ADD PARTITION p_hash_new
TABLESPACE data_hash_new;

Long-Term Solutions

1. Implement Partition Lifecycle Management

Automated Partition Management

-- Create procedure for automatic partition management
CREATE OR REPLACE PROCEDURE manage_partition_lifecycle AS
  CURSOR part_cursor IS
    SELECT
      table_owner,
      table_name,
      partition_name,
      tablespace_name,
      high_value,
      num_rows,
      last_analyzed
    FROM dba_tab_partitions
    WHERE table_owner NOT IN ('SYS', 'SYSTEM')
      AND num_rows > 0;

  v_partition_date DATE;
  v_current_date DATE := SYSDATE;
  v_months_old NUMBER;
BEGIN
  FOR part_rec IN part_cursor LOOP
    BEGIN
      -- Extract date from partition high value (for date-based partitions)
      EXECUTE IMMEDIATE 'SELECT ' || part_rec.high_value || ' - 1 FROM dual'
        INTO v_partition_date;

      v_months_old := MONTHS_BETWEEN(v_current_date, v_partition_date);

      -- Archive old partitions (older than 12 months)
      IF v_months_old > 12 THEN
        -- Move to archive tablespace
        EXECUTE IMMEDIATE 'ALTER TABLE ' || part_rec.table_owner || '.' ||
                         part_rec.table_name || ' MOVE PARTITION ' ||
                         part_rec.partition_name || ' TABLESPACE archive_data COMPRESS';

        -- Log the action
        INSERT INTO partition_maintenance_log (
          action_date, table_owner, table_name, partition_name,
          action_type, details
        ) VALUES (
          SYSDATE, part_rec.table_owner, part_rec.table_name,
          part_rec.partition_name, 'ARCHIVE',
          'Moved to archive tablespace and compressed'
        );
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        -- Log errors but continue
        INSERT INTO partition_maintenance_log (
          action_date, table_owner, table_name, partition_name,
          action_type, error_message
        ) VALUES (
          SYSDATE, part_rec.table_owner, part_rec.table_name,
          part_rec.partition_name, 'ERROR', SQLERRM
        );
    END;
  END LOOP;

  COMMIT;
END;
/

-- Create maintenance log table
CREATE TABLE partition_maintenance_log (
  action_date DATE,
  table_owner VARCHAR2(30),
  table_name VARCHAR2(30),
  partition_name VARCHAR2(30),
  action_type VARCHAR2(20),
  details VARCHAR2(500),
  error_message VARCHAR2(500)
);

Interval Partitioning Setup

-- Convert to interval partitioning for automatic partition creation
-- Example for monthly interval partitioning

-- First, create the base partitioned table structure
CREATE TABLE sales_interval (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER,
  customer_id NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (data_2024_01, data_2024_02, data_2024_03)  -- Tablespace list
(
  PARTITION p_before_2024 VALUES LESS THAN (DATE '2024-01-01') TABLESPACE data_2023
);

-- For existing tables, you'll need to reorganize
-- 1. Create new interval partitioned table
-- 2. Insert data from old table
-- 3. Drop old table and rename new table

2. Partition Space Monitoring

Comprehensive Partition Monitoring

-- Create partition space monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_partition_space AS
  CURSOR part_space_cursor IS
    SELECT
      tp.table_owner,
      tp.table_name,
      tp.partition_name,
      tp.tablespace_name,
      ds.bytes/1024/1024 as partition_size_mb,
      tu.used_percent as tablespace_used_pct,
      CASE
        WHEN tu.used_percent > 90 THEN 'CRITICAL'
        WHEN tu.used_percent > 80 THEN 'WARNING'
        WHEN tu.used_percent > 70 THEN 'INFO'
        ELSE 'OK'
      END as alert_level
    FROM dba_tab_partitions tp
    LEFT JOIN dba_segments ds ON (tp.table_owner = ds.owner
                                 AND tp.table_name = ds.segment_name
                                 AND tp.partition_name = ds.partition_name)
    LEFT JOIN dba_tablespace_usage_metrics tu ON tp.tablespace_name = tu.tablespace_name
    WHERE tp.table_owner NOT IN ('SYS', 'SYSTEM')
      AND tu.used_percent > 70;  -- Only check partitions in tablespaces > 70% full

BEGIN
  FOR part_rec IN part_space_cursor LOOP
    -- Log partition space issues
    INSERT INTO partition_space_alerts (
      alert_time, table_owner, table_name, partition_name,
      tablespace_name, partition_size_mb, tablespace_used_pct, alert_level
    ) VALUES (
      SYSTIMESTAMP, part_rec.table_owner, part_rec.table_name,
      part_rec.partition_name, part_rec.tablespace_name,
      part_rec.partition_size_mb, part_rec.tablespace_used_pct, part_rec.alert_level
    );

    -- Auto-remediation for critical situations
    IF part_rec.alert_level = 'CRITICAL' THEN
      BEGIN
        -- Try to add datafile to tablespace
        EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || part_rec.tablespace_name ||
                         ' ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G';

        UPDATE partition_space_alerts
        SET action_taken = 'Added 1GB datafile to tablespace'
        WHERE table_owner = part_rec.table_owner
          AND table_name = part_rec.table_name
          AND partition_name = part_rec.partition_name
          AND alert_time = (SELECT MAX(alert_time) FROM partition_space_alerts
                           WHERE table_owner = part_rec.table_owner
                             AND table_name = part_rec.table_name
                             AND partition_name = part_rec.partition_name);
      EXCEPTION
        WHEN OTHERS THEN
          UPDATE partition_space_alerts
          SET action_taken = 'Failed to add datafile: ' || SQLERRM
          WHERE table_owner = part_rec.table_owner
            AND table_name = part_rec.table_name
            AND partition_name = part_rec.partition_name
            AND alert_time = (SELECT MAX(alert_time) FROM partition_space_alerts
                             WHERE table_owner = part_rec.table_owner
                               AND table_name = part_rec.table_name
                               AND partition_name = part_rec.partition_name);
      END;
    END IF;
  END LOOP;

  COMMIT;
END;
/

-- Create alerts table
CREATE TABLE partition_space_alerts (
  alert_time TIMESTAMP,
  table_owner VARCHAR2(30),
  table_name VARCHAR2(30),
  partition_name VARCHAR2(30),
  tablespace_name VARCHAR2(30),
  partition_size_mb NUMBER,
  tablespace_used_pct NUMBER,
  alert_level VARCHAR2(20),
  action_taken VARCHAR2(500)
);

-- Schedule monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_PARTITION_SPACE',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_partition_space',
    repeat_interval => 'FREQ=HOURLY',
    enabled => TRUE
  );
END;
/

3. Partition Storage Optimization

Implement Tiered Storage for Partitions

-- Create different tablespaces for different data tiers
-- Hot data (current month)
CREATE TABLESPACE hot_data
DATAFILE '/fast_ssd/oracle/hot_data01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

-- Warm data (last 6 months)
CREATE TABLESPACE warm_data
DATAFILE '/ssd/oracle/warm_data01.dbf' SIZE 20G
AUTOEXTEND ON NEXT 1G MAXSIZE 200G;

-- Cold data (older than 6 months)
CREATE TABLESPACE cold_data
DATAFILE '/slow_disk/oracle/cold_data01.dbf' SIZE 50G
AUTOEXTEND ON NEXT 1G MAXSIZE 500G
DEFAULT COMPRESS FOR OLTP;  -- Use compression for cold data

-- Procedure to move partitions based on age
CREATE OR REPLACE PROCEDURE implement_tiered_storage AS
  CURSOR part_cursor IS
    SELECT
      table_owner,
      table_name,
      partition_name,
      tablespace_name,
      high_value
    FROM dba_tab_partitions
    WHERE table_owner = 'APP_SCHEMA'
      AND table_name LIKE 'SALES_%';  -- Adjust pattern as needed

  v_partition_date DATE;
  v_months_old NUMBER;
  v_target_tablespace VARCHAR2(30);
  v_compress_option VARCHAR2(50);
BEGIN
  FOR part_rec IN part_cursor LOOP
    BEGIN
      -- Extract date from partition
      EXECUTE IMMEDIATE 'SELECT ' || part_rec.high_value || ' - 1 FROM dual'
        INTO v_partition_date;

      v_months_old := MONTHS_BETWEEN(SYSDATE, v_partition_date);

      -- Determine target tier
      IF v_months_old <= 1 THEN
        v_target_tablespace := 'HOT_DATA';
        v_compress_option := '';
      ELSIF v_months_old <= 6 THEN
        v_target_tablespace := 'WARM_DATA';
        v_compress_option := '';
      ELSE
        v_target_tablespace := 'COLD_DATA';
        v_compress_option := ' COMPRESS';
      END IF;

      -- Move partition if not in correct tablespace
      IF part_rec.tablespace_name != v_target_tablespace THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || part_rec.table_owner || '.' ||
                         part_rec.table_name || ' MOVE PARTITION ' ||
                         part_rec.partition_name || ' TABLESPACE ' ||
                         v_target_tablespace || v_compress_option;
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        -- Log but continue
        DBMS_OUTPUT.PUT_LINE('Error moving partition ' || part_rec.partition_name || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/

Partition-Specific Strategies

1. Range Partition Management

-- Automated range partition creation for time-based data
CREATE OR REPLACE PROCEDURE create_monthly_partitions(
  p_table_owner VARCHAR2,
  p_table_name VARCHAR2,
  p_months_ahead NUMBER DEFAULT 3
) AS
  v_sql VARCHAR2(4000);
  v_partition_name VARCHAR2(30);
  v_tablespace_name VARCHAR2(30);
  v_high_value DATE;
BEGIN
  FOR i IN 1..p_months_ahead LOOP
    v_high_value := ADD_MONTHS(TRUNC(SYSDATE, 'MM'), i);
    v_partition_name := 'P_' || TO_CHAR(v_high_value, 'YYYY_MM');
    v_tablespace_name := 'DATA_' || TO_CHAR(v_high_value, 'YYYY_MM');

    -- Check if partition already exists
    SELECT COUNT(*) INTO v_sql
    FROM dba_tab_partitions
    WHERE table_owner = p_table_owner
      AND table_name = p_table_name
      AND partition_name = v_partition_name;

    IF v_sql = 0 THEN
      v_sql := 'ALTER TABLE ' || p_table_owner || '.' || p_table_name ||
               ' ADD PARTITION ' || v_partition_name ||
               ' VALUES LESS THAN (DATE ''' || TO_CHAR(v_high_value, 'YYYY-MM-DD') || ''')' ||
               ' TABLESPACE ' || v_tablespace_name;

      EXECUTE IMMEDIATE v_sql;
    END IF;
  END LOOP;
END;
/

2. List Partition Management

-- Manage list partitions based on data values
CREATE OR REPLACE PROCEDURE manage_list_partitions AS
  CURSOR new_values_cursor IS
    SELECT DISTINCT region_code
    FROM staging_table st
    WHERE NOT EXISTS (
      SELECT 1 FROM dba_tab_partitions tp
      WHERE tp.table_owner = 'SALES_SCHEMA'
        AND tp.table_name = 'REGIONAL_SALES'
        AND tp.high_value LIKE '%' || st.region_code || '%'
    );

  v_sql VARCHAR2(4000);
  v_partition_name VARCHAR2(30);
BEGIN
  FOR val_rec IN new_values_cursor LOOP
    v_partition_name := 'P_REGION_' || val_rec.region_code;

    v_sql := 'ALTER TABLE sales_schema.regional_sales ' ||
             'ADD PARTITION ' || v_partition_name ||
             ' VALUES (''' || val_rec.region_code || ''') ' ||
             'TABLESPACE data_regions';

    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;
/

Related Errors

• ORA-01653 - Unable to extend table
• ORA-01654 - Unable to extend index
• ORA-01655 - Unable to extend cluster
• ORA-14400 - Inserted partition key does not map to any partition

Quick Reference

Emergency Response Steps

1. ✓ Identify the failing partition and its tablespace
2. ✓ Check tablespace space usage for that partition
3. ✓ Add space to the partition’s tablespace immediately
4. ✓ Consider moving partition to different tablespace if needed
5. ✓ Implement monitoring to prevent future occurrences
6. ✓ Review partition strategy and lifecycle management

Quick Commands

-- Find partition tablespace usage
SELECT tp.partition_name, tp.tablespace_name, tu.used_percent
FROM dba_tab_partitions tp, dba_tablespace_usage_metrics tu
WHERE tp.tablespace_name = tu.tablespace_name
  AND tp.table_owner = 'SCHEMA' AND tp.table_name = 'TABLE'
ORDER BY tu.used_percent DESC;

-- Add space to partition tablespace
ALTER TABLESPACE partition_ts ADD DATAFILE SIZE 2G AUTOEXTEND ON;

-- Move partition to different tablespace
ALTER TABLE schema.table MOVE PARTITION partition_name TABLESPACE new_ts;

-- Check partition sizes
SELECT partition_name, ROUND(bytes/1024/1024, 2) as size_mb
FROM dba_segments s, dba_tab_partitions p
WHERE s.owner = p.table_owner AND s.segment_name = p.table_name
  AND s.partition_name = p.partition_name
  AND p.table_owner = 'SCHEMA' AND p.table_name = 'TABLE'
ORDER BY bytes DESC;