ORA-01548 & drop Undo tablespace

Recently I was cloning one RAC database to single instance database, and afterwards I wanted to remove one of the Undo tablespaces. Unfortunately, it didn’t go so smoothly as I expected…

SQL> drop tablespace UNDOTBS2 including contents and datafiles;
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU39_1583257383$' found, terminate dropping tablespace

Hmmm, ok – let’s check why:

SQL> select segment_name,status from dba_rollback_segs where tablespace_name = 'UNDOTB2' and status != 'OFFLINE';

SEGMENT_NAME                   STATUS
------------------------------ ----------------------------------------------------------------
_SYSSMU39_1583257383$          PARTLY AVAILABLE
_SYSSMU40_2790682469$          PARTLY AVAILABLE
_SYSSMU64_2312563332$          PARTLY AVAILABLE
_SYSSMU72_370900394$           PARTLY AVAILABLE
_SYSSMU79_210856562$           PARTLY AVAILABLE
_SYSSMU186_2826375952$         PARTLY AVAILABLE
_SYSSMU193_3230612747$         PARTLY AVAILABLE

7 rows selected.


SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE';

  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
        39          8    1790801 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        40         14    3007703 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        64         21    1766227 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        72         28    3720950 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        79         30    1263186 PREPARED         SCO|COL|REV|DEAD|EXTDTX
       186          0     381814 PREPARED         SCO|COL|REV|DEAD|EXTDTX
       193         26     471483 PREPARED         SCO|COL|REV|DEAD|EXTDTX

7 rows selected.


SQL> col GLOBAL_TRAN_ID for a30
SQL> SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                 STATE            MIXED        COMMIT#
---------------------- ------------------------------ ---------------- ------------ ----------------
64.21.1766227          48801.4B6F90FDD3B111E02D21     prepared         no           8444206153031
79.30.1263186          48801.144C46F922F511E02D21     prepared         no           8458822851795
39.8.1790801           48801.76CC934FA73211E02D21     prepared         no           8458821251112
40.14.3007703          48801.76D6934FA73211E02D21     prepared         no           8458821251099
72.28.3720950          48801.76C8934FA73211E02D21     prepared         no           8458821250987
193.26.471483          48801.76CE934FA73211E02D21     prepared         no           8458821250807
186.0.381814           48801.76D0934FA73211E02D21     prepared         no           8458821251271

7 rows selected.



SQL> SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;

LOCAL_TRAN_ID          IN_OUT       DATABASE    I
---------------------- ------------ ----------- -
64.21.1766227          in           jdbc_3      N
64.21.1766227          in           jdbc_22     N
79.30.1263186          in           jdbc_10     N
79.30.1263186          in           jdbc_49     N
39.8.1790801           in           jdbc_10     N
39.8.1790801           in           jdbc_46     N
40.14.3007703          in           jdbc_56     N
40.14.3007703          in           jdbc_44     N
72.28.3720950          in           jdbc_44     N
72.28.3720950          in           jdbc_22     N
193.26.471483          in           jdbc_4      N
193.26.471483          in           jdbc_52     N
186.0.381814           in           jdbc_28     N
186.0.381814           in           jdbc_41     N

14 rows selected.

Well, there were some “zombie” transactions around…

Because I don’t know how that happened, I’ll provide you only the solution how to remove such dead transactions:

set serveroutput on
alter system disable distributed recovery;

begin
    for rec in (SELECT KTUXEUSN ||'.'|| KTUXESLT ||'.'|| KTUXESQN as local_trx_id /* Transaction ID */
                  FROM x$ktuxe
                 WHERE ktuxesta!='INACTIVE'
                )
    loop
        
        set transaction use rollback segment SYSTEM;
        delete from sys.pending_trans$ where local_tran_id = rec.local_trx_id;
        delete from sys.pending_sessions$ where local_tran_id = rec.local_trx_id;
        delete from sys.pending_sub_sessions$ where local_tran_id = rec.local_trx_id;
        --commit;
        
        insert into pending_trans$ (
        LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR,RECO_VECTOR, TYPE#, FAIL_TIME,RECO_TIME)
        values( rec.local_trx_id, 306206, /* */
        'XXXXXXX.12345.1.2.3', 'prepared','P', hextoraw( '00000001' ), hextoraw( '00000000' ),0, sysdate, sysdate );

        insert into pending_sessions$
        values( rec.local_trx_id, 1, hextoraw('05004F003A1500000104'), 'C', 0, 30258592, '', 146);

        commit;

        EXECUTE IMMEDIATE 'commit force '''|| rec.local_trx_id ||'''';

        DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(rec.local_trx_id);

        commit;
    end loop;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
/

alter system enable distributed recovery;

Finally, I was able to drop the UNDOTBS2 tablespace:

SQL> select segment_name,status from dba_rollback_segs where tablespace_name = 'UNDOTB2' and status != 'OFFLINE';

no rows selected

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

Hope it helps someone.