Structure - Undo

Display the rollback segments

select segment_name
, status
from dba_rollback_segs
/
Output:

_SYSSMU17$                     ONLINE
_SYSSMU18$                     ONLINE
_SYSSMU19$                     OFFLINE
_SYSSMU20$                     OFFLINE

4 rows selected.

Alter undo retention

alter system set undo_retention=500 scope=memory;

What's in undo

select tablespace_name
, status
, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name
, status
/
Output:

TABLESPACE_NAME                          STATUS     HOW_MANY
---------------------------------------- -------- ----------
UNDOTBS2                                 EXPIRED         129
UNDOTBS2                                 UNEXPIRED         3

2 rows selected.

Is anything rolling back at the moment?

Look for the used_ublk value decreasing. If it is, the session connected with it is rolling back. When it reaches zero, rollback is complete.

set lines 100 pages 999
col username format a15
col command format a20
select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr
/
Output:

USERNAME        COMMAND                    USED_UBLK
--------------- -------------------- ---------------
MTDFUSR         sqlplus.exe                        1
ACTUATE         fctsrvr9.exe                       1
APPS            frmweb@pmnblx1104 (                1
APPS            Toad.exe                           1
APPS            JDBC Thin Client                   1