Oracle 手工清除回滚段的几种方法
转自：Blog： http://blog.csdn.net/tianlesoftware
关于回滚段的问题，之前也小整理过一个，参考：

       Current online Redo 和 Undo 损坏的处理方法

       http://blog.csdn.net/tianlesoftware/article/details/6261475



     Roger同学昨天整理了一个更加详细的说明，转帖过来。 Roger 的原文链接如下： http://www.killdb.com/?p=196





       某些情况下，我们需要手动去清除一些有问题的回滚段，如果该回滚段中包含活动事务，那么使用正常的方式将无法drop，所以此时你dropundo tablespace 也将失败。



可能就会遇到如下的错误：

SQL> drop tablespace undotbs1 includingcontents and datafiles;

drop tablespace undotbs1 including contentsand datafiles

*

ERROR at line 1:

ORA-01561: failed to remove all objects inthe tablespace specified



方法一：用隐含参数

SQL> show parameter undo

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL> create undo tablespace undotbs2datafile '/oracle/product/oradata/roger/undotbs2.dbf'

2  size 50m autoextend off;

Tablespace created.

SQL> create undo tablespace undotbs3datafile '/oracle/product/oradata/roger/undotbs3.dbf'

2  size 50m autoextend off;

Tablespace created.

SQL>

SQL> conn roger/roger

Connected.

SQL> create table ht_01 as select * fromdba_objects where rownum <10;

Table created.

SQL> delete from ht_01 where rownum<5;

4 rows deleted.

SQL> -----不提交

SQL> selectowner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS

2  ,tablespace_name from dba_rollback_segs;

OWNER SEGMENT_NAME   SEGMENT_ID    FILE_ID  BLOCK_ID  STATUS   TABLESPACE_NAME

------ -------------- ---------- --------------------  -------- ---------------

SYS   SYSTEM                  0          1          9 ONLINE   SYSTEM

PUBLIC _SYSSMU1$               1          2          9 ONLINE   UNDOTBS1

PUBLIC _SYSSMU2$               2          2         25 ONLINE   UNDOTBS1

PUBLIC _SYSSMU3$               3          2         41 ONLINE   UNDOTBS1
<