如何重建Undotablespace？

如何重建undo tablespace

問題描述：

  由于undotbs1过大，导致File system：/THALR_D00空间爆满，达到100%，必須將undotbs1的datafile刪除以釋放空間。

解決方法：

   1、drop tablespace undotbs1 including contents and   datafiles。

   2、新建一新的undo tablespace，如：undotbs2，

   3、alter system set undo_tablspace=undotbs2 scope=both;

操作步驟：

 

1、SQL> shutdown abort

2、SQL> alter database datafile undotbs101.dbf offline drop

3、SQL> alter database open;

 報錯如下：  

  alter database open

  *

  ERROR at line 1：
  ORA-01092: ORACLE instance terminated. Disconnection forced  

4、把database開啓為mount狀態

  SQL> startup restrict mount;

  SQL> create pfile='$ORACLE_HOME/dbs/pfilethalr.ora' from

  spfile='$ORACLE_HOME/dbs/spfilethalr.ora';

5、查詢回滾段信息

  SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU11$                      OFLINE
_SYSSMU12$                      OFLINE
_SYSSMU13$                      OFLINE
_SYSSMU14$                      OFLINE
_SYSSMU15$                      OFLINE
_SYSSMU16$                      OFLINE
_SYSSMU23$                      ONLINE
_SYSSMU24$                      ONLINE
_SYSSMU25$                      ONLINE
_SYSSMU26$                      ONLINE

11 rows selected.

6、修改pfilethalr.ora中的參數

   *.undo_managerment='MANUAL' -->原來為‘AUTO’

   _OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$)

7、新建undotbs2，通過pfile開啓database,並將undotbs1 drop掉

SQL> create undo tablespace undotbs2   

    datafile '/THALR_D00/undotbs201.dbf' size 100M;

SQL> startup pfile='/$ORACLE_HOME/dbs/pfilethalr.ora';

SQL> DROP ROLLBACK SEGMENT "_SYSSMU11$";

SQL> DROP ROLLBACK SEGMENT "_SYSSMU12$";

SQL> DROP ROLLBACK SEGMENT "_SYSSMU13$";

SQL> DROP ROLLBACK SEGMENT "_SYSSMU14$";

SQL> DROP ROLLBACK SEGMENT "_SYSSMU15$";

SQL> DROP ROLLBACK SEGMENT "_SYSSMU16$";

SQL> drop tablespace undotbs1 including contents and datafiles;

8、將undo tablespace 設置為undotbs2

SQL> alter system set undo_tablespace=undotbs2 scope=both;

9、正常開啓database

SQL> alter database open;