ORA-01595: Error Freeing Extent of Rollback Segment
ORA-01595 is an Oracle database error that occurs when the database engine fails to release (free) an extent belonging to a rollback segment during transaction processing or segment shrink operations. Rollback segments store before-images of data to support transaction rollback and read consistency, making them critical to database stability. When this error appears, it often signals underlying storage corruption, misconfigured segment parameters, or tablespace space issues that require immediate attention.
Top 3 Causes
1. Rollback Segment Header Block Corruption
Physical corruption of the rollback segment header block prevents Oracle from reading the extent chain, causing ORA-01595. This is commonly triggered by abrupt instance shutdowns, hardware I/O failures, or storage-level errors.
-- Check rollback segment status and identify problematic segments
SELECT rn.name AS segment_name,
rs.extents,
rs.rssize AS current_size_bytes,
rs.optsize AS optimal_size_bytes,
rs.hwmsize AS hwm_bytes,
rs.xacts AS active_transactions,
rs.status
FROM v$rollstat rs
JOIN v$rollname rn ON rs.usn = rn.usn
ORDER BY rs.xacts DESC;
-- Check for block corruption using DBVERIFY (run at OS level)
-- dbv FILE=/oracle/oradata/orcl/rbs01.dbf BLOCKSIZE=8192
-- Verify segment details from data dictionary
SELECT segment_name,
status,
tablespace_name,
file_id,
block_id,
initial_extent,
next_extent
FROM dba_rollback_segs
WHERE status NOT IN ('OFFLINE', 'INVALID');
2. Aggressive OPTIMAL Parameter Setting
When an OPTIMAL storage parameter is set too small, Oracle repeatedly attempts to shrink the rollback segment back to that size. This constant shrinking cycle can cause extent-freeing failures, especially when active transactions are present during the shrink operation.
-- Identify rollback segments with excessive shrink activity
SELECT rn.name AS segment_name,
rs.shrinks,
rs.wraps,
rs.optsize / 1024 / 1024 AS optimal_mb,
rs.hwmsize / 1024 / 1024 AS hwm_mb,
rs.rssize / 1024 / 1024 AS current_mb
FROM v$rollstat rs
JOIN v$rollname rn ON rs.usn = rn.usn
WHERE rs.shrinks > 0
ORDER BY rs.shrinks DESC;
-- Disable OPTIMAL to stop aggressive shrinking
ALTER ROLLBACK SEGMENT rbs_problematic STORAGE (OPTIMAL UNLIMITED);
-- Or set OPTIMAL to a safe value above HWM
ALTER ROLLBACK SEGMENT rbs_normal STORAGE (OPTIMAL 200M);
3. RBS Tablespace Space Shortage or Fragmentation
Insufficient free space or heavy fragmentation in the rollback segment tablespace prevents Oracle from reorganizing extents during the free operation. In Dictionary-Managed Tablespaces, this also involves failed updates to FET$ and UET$ dictionary tables.
-- Check free space and fragmentation in the RBS tablespace
SELECT tablespace_name,
COUNT(*) AS free_chunks,
SUM(bytes) / 1024 / 1024 AS total_free_mb,
MAX(bytes) / 1024 / 1024 AS largest_chunk_mb,
MIN(bytes) / 1024 / 1024 AS smallest_chunk_mb
FROM dba_free_space
WHERE tablespace_name = 'RBS'
GROUP BY tablespace_name;
-- Add a datafile to resolve space shortage
ALTER TABLESPACE rbs
ADD DATAFILE '/oracle/oradata/orcl/rbs02.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2G;
Quick Fix Solutions
-- Step 1: Take the problematic rollback segment offline immediately
ALTER ROLLBACK SEGMENT rbs_problematic OFFLINE;
-- Step 2: Drop and recreate the segment cleanly
DROP ROLLBACK SEGMENT rbs_problematic;
CREATE ROLLBACK SEGMENT rbs_new
TABLESPACE rbs
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 2
MAXEXTENTS UNLIMITED
OPTIMAL UNLIMITED -- Disable auto-shrink to prevent recurrence
);
ALTER ROLLBACK SEGMENT rbs_new ONLINE;
-- Step 3: Migrate to Automatic Undo Management (Oracle 9i+)
-- This is the definitive long-term fix
CREATE UNDO TABLESPACE undotbs_new
DATAFILE '/oracle/oradata/orcl/undotbs_new.dbf'
SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
-- Set in SPFILE and restart
ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS_NEW SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=SPFILE;
-- Step 4: Verify UNDO health after migration
SELECT status,
SUM(bytes) / 1024 / 1024 AS mb
FROM dba_undo_extents
WHERE tablespace_name = 'UNDOTBS_NEW'
GROUP BY status;
Prevention Tips
1. Switch to Automatic Undo Management (AUM)
The most effective prevention is migrating from manual rollback segments to AUM (introduced in Oracle 9i). AUM eliminates manual extent management entirely, removing the root cause of ORA-01595. Always set UNDO_MANAGEMENT=AUTO in new database builds and migrations.
2. Monitor Alert Log and UNDO Statistics Proactively
Schedule regular checks on V$UNDOSTAT and the Alert Log to catch early warning signs before they escalate.
-- Monitor UNDO usage trends (run daily)
SELECT TO_CHAR(begin_time, 'MM/DD HH24:MI') AS period,
undoblks AS undo_blocks_used,
txncount AS transactions,
maxquerylen AS longest_query_sec,
ssolderrcnt AS ora01555_errors,
nospaceerrcnt AS space_errors
FROM v$undostat
WHERE begin_time > SYSDATE - 1
ORDER BY begin_time DESC;
-- Alert Log error scan (12c and above)
SELECT originating_timestamp,
SUBSTR(message_text, 1, 200) AS error_message
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-015%'
AND originating_timestamp > SYSDATE - 7
ORDER BY originating_timestamp DESC;
Pro Tip: Always size your UNDO/RBS tablespace to at least 3× the expected peak transaction volume, and never set OPTIMAL below the observed High Water Mark of the rollback segment.
📖 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)