UNDO tablespace growing non-stop

Recently I found that the UNDO tablespace keep on growing, even after a database restart, it is still holding up the space. Creating a new UNDOTBS2 tablespace doesn't help either. 120GB "active" undo space never released.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
alter system set undo_tablespace=UNDOTBS2 scope=both;

Check the UNDO tablespace usage

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
291                              23.8125 UNEXPIRED UNDOTBS1                      
93                               378.375 UNEXPIRED UNDOTBS2                      
545                             750.9375 EXPIRED   UNDOTBS1                      
1349                           10969.625 EXPIRED   UNDOTBS2                      
1                                     64 ACTIVE    UNDOTBS2                      
18130                         121075.375 ACTIVE    UNDOTBS1                      

6 rows selected.

Checking which active transaction is holding up those spaces tells me there is none.

SELECT s.username,s.sid, t.xidusn, t.ubafil,
    t.ubablk, t.used_ublk
    FROM v$session s, v$transaction t
   WHERE s.saddr = t.ses_addr;

USERNAME           SID     XIDUSN     UBAFIL     UBABLK  USED_UBLK
----------- ---------- ---------- ---------- ---------- ----------
                    14        308          0          0          1

Rollback Segment indicated there are problem with some segments

SELECT segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS               TABLESPACE_NAME
------------------------------ -------------------- ------------------
SYSTEM                         ONLINE               SYSTEM
_SYSSMU1_3780397527$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU2_2232571081$           OFFLINE              UNDOTBS1
_SYSSMU3_2097677531$           OFFLINE              UNDOTBS1
_SYSSMU4_1152005954$           OFFLINE              UNDOTBS1
_SYSSMU5_1527469038$           OFFLINE              UNDOTBS1
_SYSSMU6_2443381498$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU7_3286610060$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU8_2012382730$           OFFLINE              UNDOTBS1
_SYSSMU9_1424341975$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU10_3550978943$          OFFLINE              UNDOTBS1
_SYSSMU11_3715213117$          ONLINE               UNDOTBS2
_SYSSMU12_3563522984$          ONLINE               UNDOTBS2
_SYSSMU13_1284218525$          ONLINE               UNDOTBS2
_SYSSMU14_1624181735$          ONLINE               UNDOTBS2
_SYSSMU15_1102482685$          ONLINE               UNDOTBS2
_SYSSMU16_1909532494$          ONLINE               UNDOTBS2
_SYSSMU17_3453924897$          ONLINE               UNDOTBS2
_SYSSMU18_4116382225$          ONLINE               UNDOTBS2
_SYSSMU19_606215510$           ONLINE               UNDOTBS2
_SYSSMU20_1963701883$          ONLINE               UNDOTBS2

21 rows selected.

SELECT
    LISTAGG(''''||segment_name||'''', ',') WITHIN GROUP(
        ORDER BY
            segment_name
    )
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

SELECT '"drop rollback segment "'||segment_name||'";'
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

Let's kill the problem segments

create pfile from spfile

Add this to the end of the pfile
paris._offline_rollback_segments=('_SYSSMU1_3780397527$','_SYSSMU6_2443381498$','_SYSSMU7_3286610060$','_SYSSMU9_1424341975$')

shutdown immediate;
startup pfile='E:\oracle\db_home\product\12.2.0\dbhome_1\database\INITPARIS.ORA'

drop rollback segment "_SYSSMU1_3780397527$";
drop rollback segment "_SYSSMU6_2443381498$";
drop rollback segment "_SYSSMU7_3286610060$";
drop rollback segment "_SYSSMU9_1424341975$";
drop tablespace UNDOTBS1 including contents and datafiles;

shutdown immediate;
startup;

Check the UNDO tablespace usage again

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
                162                  678 UNEXPIRED UNDOTBS2                      
                319               2840.5 EXPIRED   UNDOTBS2