标题: ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / ... [打印本页] 作者: 刘泽宇 时间: 2024-8-25 12:31 标题: 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;
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.