ORA-01555 Snapshot Too Old During Oracle Data Pump Export

Fixing ORA-01555 Snapshot Too Old During Oracle Data Pump Export

Symptoms – Oracle snapshot too old, ORA-01555, undo retention, expdp error, undo tablespace sizing, Oracle Data Pump export error, snapshot too old expdp

📌 Overview

One of the most common and frustrating errors during Oracle Data Pump exports is:

ORA-01555: snapshot too old: rollback segment number with name "" too small


This error typically occurs when a long-running query or export operation is unable to access the required undo information — because it has already been overwritten.

In this guide, we will break down:

• What causes ORA-01555
• How to calculate the undo space needed
• How to resize your undo tablespace (especially if it’s a Bigfile tablespace)
• Best practices to prevent this error in future exports

🔍 What Causes ORA-01555 in expdp?

Oracle uses undo segments to store old versions of data so it can provide read consistency. During a Data Pump export (expdp), Oracle tries to take a consistent snapshot of your data.

If the export runs long and the undo retention is not high enough — or if your undo tablespace is too small — Oracle will purge undo data before the export completes. The result? ORA-01555.

✅ Step-by-Step Solution

Step 1: Check Your Undo Configuration

Run the following SQL to view your current undo setup:

SHOW PARAMETER undo;


You’ll get output like:

Parameter Value

undo_retention 43200

undo_tablespace UNDOTBS1

undo_management AUTO

🔎 This means the system is set to retain undo for 12 hours (43,200 seconds).

Step 2: Estimate Undo Generation Rate (UGR)

Oracle tracks historical undo activity in DBA_HIST_UNDOSTAT.

Run this SQL to find out your peak undo generation rate:

SELECT
ROUND(MAX(undoblks * TO_NUMBER(p.value) / 900)) AS bytes_per_sec
FROM
dba_hist_undostat u,
v$parameter p
WHERE
p.name = 'db_block_size';


Step 3: Calculate Total Undo Space Required

Now calculate:

Undo Space Needed = Undo Generation Rate * UNDO_RETENTION


Use this SQL for a full calculation:

SELECT
u.bytes_per_sec,
ur.undo_retention,
ROUND(u.bytes_per_sec * ur.undo_retention / 1024 / 1024) AS undo_space_mb_needed
FROM
(
SELECT
ROUND(MAX(undoblks * TO_NUMBER(p.value) / 900)) AS bytes_per_sec
FROM
dba_hist_undostat u,
v$parameter p
WHERE
p.name = 'db_block_size'
) u,
(
SELECT TO_NUMBER(value) AS undo_retention
FROM v$parameter
WHERE name = 'undo_retention'
) ur;


💡 This will tell you how many MB of undo space you need to retain data for your configured retention time.

Step 4: Check Undo Tablespace Size

Run this to check the actual size of your undo tablespace:

SELECT
tablespace_name,
ROUND(SUM(bytes)/1024/1024) AS size_mb
FROM
dba_data_files
WHERE
tablespace_name = 'UNDOTBS1'
GROUP BY
tablespace_name;


If the result is less than what you calculated in Step 3, you’re under-provisioned. That’s likely what’s causing the ORA-01555 error.

Step 5: Resize Undo Tablespace (for Bigfile or Smallfile)

🔹 If you’re using a Bigfile Tablespace:

Check it using:

SELECT tablespace_name, bigfile
FROM dba_tablespaces
WHERE tablespace_name = 'UNDOTBS1';


Then resize it with:

ALTER DATABASE DATAFILE '/path_to_data/undotbs01.dbf' RESIZE 70G;


Or enable autoextend:

ALTER DATABASE DATAFILE '/path_to_data/undotbs01.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 128G;


🔸 If you’re using a Smallfile Tablespace:

Add more datafiles instead:

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/path_to_data/undotbs02.dbf' SIZE 10G AUTOEXTEND ON;


🛡️ Best Practices to Prevent ORA-01555

✅ Use direct path exportsAdd DATA_ACCESS_METHOD=direct_path in your expdp

✅ Export large tables separatelyUse the TABLES= or QUERY= parameter

✅ Increase undo_retention temporarilyBefore starting export, bump it up if needed

✅ Avoid heavy DMLs during exportCompeting DML can use up undo

✅ Monitor undo pressure regularlyUse DBA_HIST_UNDOSTAT to spot trends

🏁 Summary

The ORA-01555: snapshot too old error during Oracle Data Pump export is usually caused by insufficient undo space or low undo retention. You can fix and prevent it by:

• Calculating the actual undo space needed
• Ensuring your undo tablespace is sized accordingly
• Using Bigfile tablespaces and autoextend to scale dynamically

By proactively managing undo, you’ll avoid this error during exports, long-running queries, and flashback operations.

Author: Rohit Mishri