ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / ...

刘泽宇

现象：
After creating a new Undo Tablespace, you are trying to drop the old Undo Tablespace and are getting the following error:

ORA-01548: active rollback segment '_SYSSMU51$' found, terminate dropping


原因：
In most cases, this error is legitimate in that there is an active transaction still showing up as using the Undo Segment that is being referenced and we will need to clear that transaction before you will be able to drop the undo tablespace.

处理方法：
In this particular case there was a Distributed Transaction that was Prepared but Dead.

1). Check the status of the undo segments that are not either Online or Offline:

Select segment_id,segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') ;

You find you have segments that are 'Partly Available'

This usually means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back.

2). Use the following query to check for any Active Transactions on the Undo Segments reported above.

To check for any active transactions on a rollback segment:

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
AND ktuxeusn=<SEGMENT_ID>                   <<<<<<<<<< Replace <SEGMENT_ID> from point no. 1 output
/

EXAMPLE:

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

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ---------------- ------------------------
265 31 806703 PREPARED SCO|COL|REV|DEAD|EXTDTX

3). In the above case we can see that Undo Segment 265 has an Active Transaction that is showing as

PREPARED and DEAD

This is part of a distributed transaction. The DEAD indicates it is a failed distributed transaction but as it is PREPARED it may need to commit or roll back.

4). You can use the following queries to check for in doubt transactions:

SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;

SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;

In this case, we found no rows where returned from either query.

5). At this point you have to cleanup Distributed transaction without corresponding dba_2pc entries

Please Contact Oracle Support to clean the stranded dba_2pc transactions

6). Once these transactions have been cleared, you should now be able to drop the undo tablespace with out getting the ORA-1548.