今天开发商告诉我undotbs1无法删除：

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU205_2862632269$' found, terminate
dropping tablespace

查看了一下v$rollstat:

SQL> select usn,status from v$rollstat;
SQL> select segment_name,status from dba_rollback_segs where status='PARTLY AVAILABLE';
SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU205_2862632269$ PARTLY AVAILABLE
_SYSSMU709_2095277529$ PARTLY AVAILABLE
_SYSSMU713_2588109099$ PARTLY AVAILABLE

回滚段没有正常回滚。

ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / Dead [ID 1321093.1]
SQL> select usn,status from v$rollstat;
SQL> select segment_name,status from dba_rollback_segs where status='PARTLY AVAILABLE';
SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU205_2862632269$ PARTLY AVAILABLE
_SYSSMU709_2095277529$ PARTLY AVAILABLE
_SYSSMU713_2588109099$ PARTLY AVAILABLE

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU205_2862632269$' found, terminate
dropping tablespace
SQL> select segment_name,status from dba_rollback_segs where segment_name='_SYSSMU205_2862632269$';

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU205_2862632269$ PARTLY AVAILABLE

SQL> select select segment_name,status from dba_rollback_segs where

SQL> select segment_name,status from dba_rollback_segs where status='PARTLY AVAILABLE';

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU205_2862632269$ PARTLY AVAILABLE
_SYSSMU709_2095277529$ PARTLY AVAILABLE
_SYSSMU713_2588109099$ PARTLY AVAILABLE

SQL>
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU205_2862632269$' found, terminate
dropping tablespace

SQL> desc dba_rollback_segs
Name Null? Type
----------------------------------------- --------
SEGMENT_NAME NOT NULL VARCHAR2(30)
OWNER VARCHAR2(6)
TABLESPACE_NAME NOT NULL VARCHAR2(30)
SEGMENT_ID NOT NULL NUMBER
FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NOT NULL NUMBER
PCT_INCREASE NUMBER
STATUS VARCHAR2(16)
INSTANCE_NUM VARCHAR2(40)
RELATIVE_FNO NOT NULL NUMBER

SQL> select segment_name,segment_id from dba_rollback_segs where status='PARTLY AVAILABLE';

SEGMENT_NAME SEGMENT_ID
------------------------------ ----------
_SYSSMU205_2862632269$ 205
_SYSSMU709_2095277529$ 709
_SYSSMU713_2588109099$ 713

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
2 KTUXESTA Status,
3 KTUXECFL Flags
4 FROM x$ktuxe
5 WHERE ktuxesta!='INACTIVE'
6 AND ktuxeusn in (205,709,713);

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ----------------
205 25 16645 PREPARED SCO|COL|REV|DEAD|EXTDTX
709 33 37840 PREPARED SCO|COL|REV|DEAD|EXTDTX
713 12 11085 PREPARED SCO|COL|REV|DEAD|EXTDTX

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
2 KTUXESTA Status,
3 KTUXECFL Flags
4 FROM x$ktuxe
5 WHERE ktuxesta!='INACTIVE'
6 AND ktuxeusn in (205,709,713,337);

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ----------------
205 25 16645 PREPARED SCO|COL|REV|DEAD|EXTDTX
337 6 344 ACTIVE NONE
709 33 37840 PREPARED SCO|COL|REV|DEAD|EXTDTX
713 12 11085 PREPARED SCO|COL|REV|DEAD|EXTDTX
SQL> col GLOBAL_TRAN_ID for a30
SQL> set linesize 200
SQL> SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ----------------
205.25.16645 113577.12CD09FFC10998EA492E22F prepared no 11432940912739
22C804DCEEB39B97F

713.12.11085 113577.FD8BB4B3CDB74CDC94CF422 prepared no 11430040235570
BFE9D23458633701C

709.33.37840 113577.BB19CE47BE6364256ABD430 prepared no 11436698621666
F8608A4EDF476525B

859.25.10479 113577.DDC18D6841CAEB04B87495B forced rollback no 11436698621646
B56480730794BED5F
SQL> SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ---------------
205.25.16645 113577.12CD09FFC10998EA492E22F prepared no 11432940912739
22C804DCEEB39B97F

713.12.11085 113577.FD8BB4B3CDB74CDC94CF422 prepared no 11430040235570
BFE9D23458633701C

709.33.37840 113577.BB19CE47BE6364256ABD430 prepared no 11436698621666
F8608A4EDF476525B

859.25.10479 113577.DDC18D6841CAEB04B87495B forced rollback no 11436698621646
B56480730794BED5F

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ---------------
SQL> SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;

LOCAL_TRAN_ID IN_ DATABASE I
---------------------- --------------- ------
709.33.37840 in NULLxa.oracle.com N
859.25.10479 in NULLxa.oracle.com N

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

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ----------------
205.25.16645 113577.12CD09FFC10998EA492E22F prepared no 11432940912739
22C804DCEEB39B97F

713.12.11085 113577.FD8BB4B3CDB74CDC94CF422 prepared no 11430040235570
BFE9D23458633701C

709.33.37840 113577.BB19CE47BE6364256ABD430 prepared no 11436698621666
F8608A4EDF476525B

859.25.10479 113577.DDC18D6841CAEB04B87495B forced rollback no 11436698621646
B56480730794BED5F

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ---------------
SQL> commit force 205.25.16645;
commit force 205.25.16645
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> commit force '205.25.16645';
Commit complete.
SQL> SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ---------------
205.25.16645 113577.12CD09FFC10998EA492E22F forced commit no
22C804DCEEB39B97F

713.12.11085 113577.FD8BB4B3CDB74CDC94CF422 prepared no 11430040235570
BFE9D23458633701C

709.33.37840 113577.BB19CE47BE6364256ABD430 prepared no 11436698621666
F8608A4EDF476525B

859.25.10479 113577.DDC18D6841CAEB04B87495B forced rollback no 11436698621646
B56480730794BED5F

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ----------------
SQL> commit force '713.12.11085';
Commit complete.
SQL> commit force '
2 709.33.37840';
commit force '
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID
709.33.37840
SQL> commit force '709.33.37840';
Commit complete.
SQL> SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ------------
205.25.16645 113577.12CD09FFC10998EA492E22F forced commit no
22C804DCEEB39B97F

713.12.11085 113577.FD8BB4B3CDB74CDC94CF422 forced commit no
BFE9D23458633701C

709.33.37840 113577.BB19CE47BE6364256ABD430 forced commit no
F8608A4EDF476525B

859.25.10479 113577.DDC18D6841CAEB04B87495B forced rollback no 11436698621646
B56480730794BED5F

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ------------
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU205_2862632269$' found, terminate dropping tablespace
SQL> select segment_name,segment_id from dba_rollback_segs where status='PARTLY AVAILABLE';
no rows selected
SQL> select segment_name,segment_id from dba_rollback_segs where status not in ('ONLINE','OFFLINE') and rownum<=10;
no rows selected
SQL> SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
---------------------- ------------------------------ ----------
205.25.16645 113577.12CD09FFC10998EA492E22F forced commit no
22C804DCEEB39B97F

713.12.11085 113577.FD8BB4B3CDB74CDC94CF422 forced commit no
BFE9D23458633701C

709.33.37840 113577.BB19CE47BE6364256ABD430 forced commit no
F8608A4EDF476525B

859.25.10479 113577.DDC18D6841CAEB04B87495B forced rollback no 11436698621646
B56480730794BED5F
SQL> select * from DBA_2PC_NEIGHBORS;
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
2 KTUXESTA Status,
3 KTUXECFL Flags
4 FROM x$ktuxe
5 WHERE ktuxesta!='INACTIVE'
6 AND ktuxeusn in (205,709,713);
no rows selected
SQL> alter tablespace undotbs1 offline;
Tablespace altered.
SQL> alter tablespace undotbs1 online;
Tablespace altered.
SQL> alter tablespace
SQL> drop tablespace undotbs1;
Tablespace dropped.