Oracle Database Discussions

Hello everyone,

I am working on a oracle 11.2.0.4 EE database (on exadata) with 2 nodes. I created a new undo tablespace one for each node and then changed UNDO_TABLESPACE parameters in each node. since this database is working 24/7 I waited for a while and wanted to drop old undo tablespaces but when I tried I got ORA-30013 "undo tablespace 'UNDOTBS01' is currently in use"

so, first I checked for the active transactions:

select b.segment_name, sum(c.used_ublk)

from dba_rollback_segs b, gv$transaction c

where b.segment_id = c.xidusn

  and b.TABLESPACE_NAME = 'UNDOTBS01'

group by segment_name

/

there is no active transaction that uses these (undotbs01 and undotbs02) tablespaces. then I thought it could be because of undo retention and waited for the retention time but still I can not drop these 2 tablespaces. any idea?

thanks.

Edit: when I run:

select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS01' and status='ONLINE';

1 row returns:

UNDOTBS01    PUBLIC    _SYSSMU343_3112705669$    ONLINE

and for undotbs2 there are 28 rows return.