Introduction Oracle ORA-01555 `snapshot too old` occurs when a long-running query needs to read undo data that has already been overwritten by newer transactions. This is one of the most common errors in Oracle databases with long-running reporting queries or exports against actively updated tables.

Symptoms - `ORA-01555: snapshot too old: rollback segment number X with name Y too small` - Long-running SELECT or EXPORT operations failing after running for hours - Query works with small data subsets but fails on the full dataset - Undo tablespace showing high turnover rate - `V$UNDOSTAT` showing `SSOLDERRCNT` increasing

Common Causes - `UNDO_RETENTION` set too low for the query duration - Undo tablespace too small, forcing early overwrite of undo records - Large DML operations (bulk UPDATE/DELETE) generating excessive undo - Auto-tuned undo retention being overridden by space pressure - Query running longer than the undo retention period

Step-by-Step Fix 1. **Check undo tablespace status and retention": ```sql -- Current undo retention SHOW PARAMETER undo_retention;

-- Undo tablespace usage SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb, SUM(CASE WHEN status = 'EXPIRED' THEN bytes ELSE 0 END) / 1024 / 1024 AS expired_mb, SUM(CASE WHEN status = 'UNEXPIRED' THEN bytes ELSE 0 END) / 1024 / 1024 AS unexpired_mb, SUM(CASE WHEN status = 'ACTIVE' THEN bytes ELSE 0 END) / 1024 / 1024 AS active_mb FROM dba_undo_extents GROUP BY tablespace_name;

-- Undo error count SELECT BEGIN_TIME, END_TIME, UNDOBLKS, SSOLDERRCNT, NOSPACEERRCNT FROM v$undostat ORDER BY BEGIN_TIME DESC FETCH FIRST 20 ROWS ONLY; ```

  1. 1.**Increase UNDO_RETENTION":
  2. 2.```sql
  3. 3.-- Set retention to exceed the longest expected query duration
  4. 4.ALTER SYSTEM SET undo_retention = 28800; -- 8 hours in seconds

-- Enable retention guarantee (requires sufficient undo tablespace) ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; ```

  1. 1.**Increase the undo tablespace size":
  2. 2.```sql
  3. 3.-- Add a datafile to the undo tablespace
  4. 4.ALTER TABLESPACE undotbs1
  5. 5.ADD DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs02.dbf'
  6. 6.SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
  7. 7.`
  8. 8.**Optimize the query to run faster":
  9. 9.```sql
  10. 10.-- Break the long query into smaller batches
  11. 11.-- Instead of:
  12. 12.-- SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2026-01-01';

-- Use monthly batches: SELECT /*+ FULL(orders) */ * FROM orders WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31';

-- Create a materialized view for frequently accessed reports CREATE MATERIALIZED VIEW mv_monthly_orders BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT TRUNC(order_date, 'MM') AS month, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY TRUNC(order_date, 'MM'); ```

Prevention - Set `undo_retention` to at least 2x the longest expected query duration - Size the undo tablespace to hold the retention period's worth of undo - Use `RETENTION GUARANTEE` for critical reporting databases (monitors space) - Schedule long-running reports during low-DML windows - Use materialized views for frequently accessed aggregate reports - Monitor `SSOLDERRCNT` in `V$UNDOSTAT` with alerting - Consider using flashback data archive for critical tables requiring long history