ORA-01552 cannot use system rollback segment for non-system tablespace

建立表的時候報

ORA-01552 cannot use system rollback segment for non-system tablespace SQL> CREATE TABLE DEPT

  2         (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

  3          DNAME VARCHAR2(14) ,

  4          LOC VARCHAR2(13) ) ;

CREATE TABLE DEPT

*

ERROR at line 1:

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

解決辦法：

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      MANUAL

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

SQL> alter system set undo_tablespace='undotbs3' scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      undotbs3

重新測試剛才出錯的語句

SQL> conn scott/tiger

Connected.

SQL>

SQL>

SQL> CREATE TABLE DEPT

  2         (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

  3          DNAME VARCHAR2(14) ,

  4          LOC VARCHAR2(13) ) ;

Table created.

我的由于沒有undotbs2，并且undo_management為手動模式，是以必須切換