ORA-01562: FAILED TO EXTEND ROLLBCK SGMT ERRORS WHEN SPACE SEEMS OK

(Doc ID 566592.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 10.2.0.3
This problem can occur on any platform.

Symptoms

You are using automatic undo management, there is enough free space in the undo tablespace.
And you see ORA-01562 Errors in the trace files.

On examining the output of querys

SELECT r.name "ROLLBACK SEGMENT NAME ",
p.pid "ORACLE PID",
p.spid "SYSTEM PID ",
NVL ( p.username , 'NO TRANSACTION'),
p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY r.name;


select s.sid, s.username, r.name "ROLLBACK SEG"
from v$session s, v$transaction t, v$rollname r
where s.taddr=t.addr
and t.xidusn = r.usn;



Most of the rollback segments show that the there are no active transaction

ROLLBACK SEGMENT NAME  ORACLE PID  SYSTEM PID   NVL(P.USERNAME, TERMINAL)

_SYSSMU427$                                                                                       NO TRANSACTION

_SYSSMU932$                                    1253                    3715                oracle UNKNOWN
.
_SYSSMU1052$                                  1950                    27030              oracle UNKNOWN
.
_SYSSMU607$                                                                                       NO TRANSACTION
.
_SYSSMU308$                                                                                       NO TRANSACTION


This problem can occur on a distributed transcation or on a normal transaction.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms