This error is all about read consistency, consider a scenario:

undo_retention is set to 15min (900 seconds), you started long running query at 9am, and the query runs for next 1 hours, any DML transaction might changed rows during query runtime and old rows (i.e. Before image) will be safe in undo tablespace for next 15min. If the same changed rows are needed by long running query after 15min then the error will appear.

Solution:

Increase undo_retention to sufficiently enough value.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_retention = 3600 scope=both;
System altered.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS1

Additionally, you need to take care about undo tablespace size by considering undo_retention, In my case, i have added the additional datafile, as below:

SQL> alter tablespace UNDOTBS1 add datafile '/oradata/datafile/undotbs_02.dbf' size 100M autoextend on next 200M maxsize 31G;

Thanks,

Stay Tune. 🙂