DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01554 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01554: Maximum Extents Reached in Segment in Tablespace

ORA-01554 occurs when an Oracle segment (table, index, or other object) has reached the maximum number of extents allowed by its storage configuration. Once this limit is hit, Oracle can no longer allocate additional space for that segment, causing all INSERT or UPDATE operations on it to fail immediately. This error is especially common in legacy systems using Dictionary Managed Tablespaces (DMT) with low MAXEXTENTS values inherited from older Oracle versions.

Top 3 Causes

1. Low MAXEXTENTS Value Set on the Segment

Older Oracle databases (7.x, 8.x) used default MAXEXTENTS values as low as 121 or 505. Legacy applications migrated to modern Oracle versions often carry these restrictive settings forward.

-- Check current MAXEXTENTS and extent usage
SELECT segment_name,
       segment_type,
       extents,
       max_extents,
       tablespace_name
FROM   dba_segments
WHERE  owner       = 'YOUR_SCHEMA'
  AND  segment_name = 'YOUR_TABLE';
Enter fullscreen mode Exit fullscreen mode

2. Excessively Small Extent Sizes (INITIAL / NEXT)

When NEXT extent size is set too small (e.g., 8KB or 64KB), even a moderately sized table can consume hundreds or thousands of extents rapidly, exhausting the MAXEXTENTS limit far sooner than expected.

-- Find segments with high extent counts close to their limit
SELECT owner,
       segment_name,
       segment_type,
       extents,
       max_extents,
       ROUND(extents / DECODE(max_extents, 0, 1, max_extents) * 100, 2) AS pct_used
FROM   dba_segments
WHERE  max_extents NOT IN (0, 2147483645)
  AND  extents / DECODE(max_extents, 0, 1, max_extents) >= 0.85
ORDER BY pct_used DESC;
Enter fullscreen mode Exit fullscreen mode

3. Dictionary Managed Tablespace Without UNLIMITED Setting

Segments residing in Dictionary Managed Tablespaces (DMT) are subject to MAXEXTENTS restrictions by default. Unlike Locally Managed Tablespaces (LMT), DMT segments do not automatically benefit from unlimited extent allocation.

-- Check tablespace management type
SELECT tablespace_name,
       extent_management,
       allocation_type
FROM   dba_tablespaces;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1 – Set MAXEXTENTS to UNLIMITED immediately

-- For a table
ALTER TABLE schema_name.table_name
  STORAGE (MAXEXTENTS UNLIMITED);

-- For an index
ALTER INDEX schema_name.index_name
  STORAGE (MAXEXTENTS UNLIMITED);
Enter fullscreen mode Exit fullscreen mode

Fix 2 – Rebuild or move the segment to consolidate extents

-- Move table to reduce extent count and reset storage
ALTER TABLE schema_name.table_name MOVE TABLESPACE target_tablespace;

-- Rebuild all indexes after MOVE
ALTER INDEX schema_name.index_name REBUILD;

-- Alternatively, use SHRINK for online consolidation
ALTER TABLE schema_name.table_name ENABLE ROW MOVEMENT;
ALTER TABLE schema_name.table_name SHRINK SPACE CASCADE;
Enter fullscreen mode Exit fullscreen mode

Fix 3 – Migrate to a Locally Managed Tablespace

-- Create a properly configured LMT
CREATE TABLESPACE new_lmt_tbs
  DATAFILE '/oradata/new_lmt_tbs01.dbf'
  SIZE 10G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

-- Move affected table to the new tablespace
ALTER TABLE schema_name.table_name MOVE TABLESPACE new_lmt_tbs;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Proactive Monitoring
Schedule a daily job using DBMS_SCHEDULER to query DBA_SEGMENTS for any segment exceeding 80% of its MAXEXTENTS limit. Alert the DBA team before the threshold is reached, not after.

-- Daily monitoring query
SELECT owner, segment_name, extents, max_extents,
       ROUND(extents / DECODE(max_extents,0,1,max_extents)*100,2) pct_used
FROM   dba_segments
WHERE  max_extents NOT IN (0, 2147483645)
  AND  extents / DECODE(max_extents,0,1,max_extents) >= 0.8
ORDER BY pct_used DESC;
Enter fullscreen mode Exit fullscreen mode

2. Always Use LMT with UNLIMITED Extents for New Objects
Establish a DBA standard that all new tablespaces use EXTENT MANAGEMENT LOCAL and all segments are created with STORAGE (MAXEXTENTS UNLIMITED). This eliminates ORA-01554 as a concern for any newly provisioned objects and dramatically reduces ongoing storage management overhead.

Related Errors

  • ORA-01653 – Unable to extend table due to insufficient physical space in the tablespace (different from ORA-01554 which is an extent count limit).
  • ORA-01656 – Max extents reached in a rollback/undo segment.
  • ORA-01652 – Unable to extend temp segment, typically seen during large sort or hash join operations.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)