ORA-01552 非系統表空間不能使用系統復原段處理

                 ORA-01552: cannot use system rollback segment for...錯誤解決

故障現象：

     今天本要做資料庫的不完全恢複，但是在準備環境的時候需要建立幾張表做為恢複用的基表。在建立表的時候

就報了如下一段錯誤：

13:56:03 SQL> create table wwl001 (id number,name varchar(12));

create table wwl001 (id number,name varchar(12))

*

ERROR at line 1:

ORA-01552: cannot use system rollback segment for non-system tablespace 'WWL'；

!!!!　非系統表空間'WWL'不能使用系統復原段

問題原因：

　　我昨天做了由于磁盤損壞導緻資料庫災難的恢複，可能是由于恢複的時候參數檔案修改了，是以才導緻成復原段

變成了手動管理模式，我們可以看看如下步驟：

詳情：

１、建立表，非系統表空間不能使用復原段

2、檢視復原段管理方式，為手動管理

13:57:24 SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      MANUAL

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

3、檢視復原段狀态

13:57:23 SQL> select segment_name, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS

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

SYSTEM                         SYSTEM                         ONLINE

_SYSSMU1$                      UNDOTBS1                       OFFLINE

_SYSSMU2$                      UNDOTBS1                       OFFLINE

_SYSSMU3$                      UNDOTBS1                       OFFLINE

_SYSSMU4$                      UNDOTBS1                       OFFLINE

_SYSSMU5$                      UNDOTBS1                       OFFLINE

_SYSSMU6$                      UNDOTBS1                       OFFLINE

_SYSSMU7$                      UNDOTBS1                       OFFLINE

_SYSSMU8$                      UNDOTBS1                       OFFLINE

_SYSSMU9$                      UNDOTBS1                       OFFLINE

_SYSSMU10$                     UNDOTBS1                       OFFLINE

11 rows selected.

4、針對現象修改undo_management參數為auto即可。

14:08:06 SQL> alter system set undo_management=auto scope=spfile;

System altered.

14:17:51 SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

14:18:22 SQL> startup

ORACLE instance started.

Total System Global Area  100663296 bytes

Fixed Size                  1217884 bytes

Variable Size              88083108 bytes

Database Buffers            8388608 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

5、檢視復原段狀态

14:18:55 SQL> select segment_name, tablespace_name, status from dba_rollback_segs;

_SYSSMU1$                      UNDOTBS1                       ONLINE

_SYSSMU2$                      UNDOTBS1                       ONLINE

_SYSSMU3$                      UNDOTBS1                       ONLINE

_SYSSMU4$                      UNDOTBS1                       ONLINE

_SYSSMU5$                      UNDOTBS1                       ONLINE

_SYSSMU6$                      UNDOTBS1                       ONLINE

_SYSSMU7$                      UNDOTBS1                       ONLINE

_SYSSMU8$                      UNDOTBS1                       ONLINE

_SYSSMU9$                      UNDOTBS1                       ONLINE

_SYSSMU10$                     UNDOTBS1                       ONLINE

6、我們再執行剛才的建表語句，非常好，可以建立表了。

14:19:51 SQL> create table wwl001 (id number,name varchar(12));

Table created.