ORA-01552: cannot use system rollback segment

SQL>  conn testhotback/testhotback;
Connected.
SQL> create table test (id number,name varchar2(10));
create table test (id number,name varchar2(10))
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-01552:	cannot use system rollback segment for non-system tablespace 'string'
Cause:	Tried to use the system rollback segment for operations involving non-system tablespace. If this is a clone database then this will happen when attempting any data modification outside of the system tablespace. Only the system rollback segment can be online in a clone database.
Action:	Create one or more private/public segment(s), shutdown and then startup again. May need to modify the INIT.ORA parameter rollback_segments to acquire private rollback segment. If this is a clone database being used for tablspace point in time recovery then this operation is not allowed. If the non-system tablespace has AUTO segment space management, then create an undo tablespace.

3 查回滚段表
SQL> select segment_name,status from dba_rollback_segs;

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------
SYSTEM                         ONLINE
_SYSSMU1$                      OFFLINE
_SYSSMU2$                      OFFLINE
_SYSSMU3$                      OFFLINE
_SYSSMU4$                      OFFLINE
_SYSSMU5$                      OFFLINE
_SYSSMU6$                      OFFLINE
_SYSSMU7$                      OFFLINE
_SYSSMU8$                      OFFLINE
_SYSSMU9$                      OFFLINE
_SYSSMU10$                     OFFLINE

11 rows selected.

SQL> select us#,name,status$ from undo$;

       US# NAME                              STATUS$
---------- ------------------------------ ----------
         0 SYSTEM                                  3
         1 _SYSSMU1$                               2
         2 _SYSSMU2$                               2
         3 _SYSSMU3$                               2
         4 _SYSSMU4$                               2
         5 _SYSSMU5$                               2
         6 _SYSSMU6$                               2
         7 _SYSSMU7$                               2
         8 _SYSSMU8$                               2
         9 _SYSSMU9$                               2
        10 _SYSSMU10$                              2

       US# NAME                              STATUS$
---------- ------------------------------ ----------
        11 MONSTER                                 1
        12 _SYSSMU12$                              1
        13 _SYSSMU13$                              1
        14 _SYSSMU14$                              1
        15 _SYSSMU15$                              1
        16 _SYSSMU16$                              1
        17 _SYSSMU17$                              1
        18 _SYSSMU18$                              1
        19 _SYSSMU19$                              1
        20 _SYSSMU20$                              1

21 rows selected.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      SYSTEM


SQL> alter system set undo_management='AUTO' scope=spfile;

System altered.


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      SYSTEM


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

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      SYSTEM

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1266392 bytes
Variable Size             138415400 bytes
Database Buffers           25165824 bytes
Redo Buffers                2924544 bytes
Database mounted.
SQL> alter system set undo_tablespace='UNDOTBS1' scope=spfile;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      SYSTEM
SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1266392 bytes
Variable Size             138415400 bytes
Database Buffers           25165824 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>


SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------
SYSTEM                         ONLINE
_SYSSMU1$                      ONLINE
_SYSSMU2$                      ONLINE
_SYSSMU3$                      ONLINE
_SYSSMU4$                      ONLINE
_SYSSMU5$                      ONLINE
_SYSSMU6$                      ONLINE
_SYSSMU7$                      ONLINE
_SYSSMU8$                      ONLINE
_SYSSMU9$                      ONLINE
_SYSSMU10$                     ONLINE

11 rows selected.

SQL> select us#,name,status$ from undo$;

       US# NAME                              STATUS$
---------- ------------------------------ ----------
         0 SYSTEM                                  3
         1 _SYSSMU1$                               3
         2 _SYSSMU2$                               3
         3 _SYSSMU3$                               3
         4 _SYSSMU4$                               3
         5 _SYSSMU5$                               3
         6 _SYSSMU6$                               3
         7 _SYSSMU7$                               3
         8 _SYSSMU8$                               3
         9 _SYSSMU9$                               3
        10 _SYSSMU10$                              3

       US# NAME                              STATUS$
---------- ------------------------------ ----------
        11 MONSTER                                 1
        12 _SYSSMU12$                              1
        13 _SYSSMU13$                              1
        14 _SYSSMU14$                              1
        15 _SYSSMU15$                              1
        16 _SYSSMU16$                              1
        17 _SYSSMU17$                              1
        18 _SYSSMU18$                              1
        19 _SYSSMU19$                              1
        20 _SYSSMU20$                              1

21 rows selected.

参考：http://space.itpub.net/12778571/viewspace-263382
          http://ora-01552.ora-code.com/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/77164/viewspace-668180/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/77164/viewspace-668180/