ORA-01555: snapshot too old: rollback segment number 91 with name “…” too small. What can be the cause?

Problem

Get ORA-01555 exception during normal regular DB operations:

ORA-01555: snapshot too old: rollback segment number 91 with name “_SYSSMU91_408937485$” too small

Cause

One of common causes could be that your UNDO tablespace is too small to serve for running DML operations.

Whenever you start a DML operation, database has to save current state of related rowset for whatever time period DML operation can take. If it is a long running statement which takes half a day – database needs to keep rowset state for this period. This is needed to have consistent dataset to provide relevant SQL result.

Even more memory is needed to process update or merge DMLs. Imagine updating few thousands of records which keep BLOBs containing 2Mb or more… Everything needs to be saved somewhere for a rollback.

So, most probably this error will be shown if you have manually extensible tablespace which got to small. Can check whether its your case by executing:

-- (1) Check whethere tablespace is autoextesible

select file_name, autoextensible from dba_data_files where tablespace_name = 'UNDOTBS2';
-- (2) Check available free space of UNDO tablespace</pre>
select
    fs.tablespace_name,
    round(ms.bytes/1024/1024,2) "Max size (Mb)",
    round(fs.total_bytes/1024/1024,2) "Available space (Mb)"
from (select
        tablespace_name,
        sum(bytes) as total_bytes
      from dba_free_space
      group by tablespace_name) fs
join dba_data_files ms on ms.tablespace_name = fs.tablespace_name
where ms.tablespace_name = 'UNDOTBS2';

Solution

If the first select statement shows that your tablespace file is not autoextensible, modify it by making autoextesible:

alter database datafile '/opt/oracle/database/oradata/promis/undotbs02.dbf' autoextend on;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0