ORA-01552: cannot use system rollback错误解决方法

/* 2008/06/1 4  星期六
＊蒙昭良
*环境：linux5 + Oracle10gR2
*ORA-01552: cannot use system rollback错误解决方法
＊
*/


SQL> connect sys/mzl as sysdba
Connected.
SQL> create table scott.test
  2  (id number(8),
  3  name varchar2(30));
create table scott.test
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'


提示系统的回滚段不能使用，可以查看回滚还原段的空间情况。

1   查看还原表空间的名称
SQL> show parameter undo

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


2  查看还原表空间的剩余空间
SQL> l
  1* select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible from dba_data_files
SQL> /

FILE_NAME                                          TABLESPACE_NAME         MB AUT
-------------------------------------------------- --------------- ---------- ---
/u01/app/oracle/oradata/orcl/risenet.dbf           RISENET
/u01/app/oracle/oradata/orcl/perfstat.dbf          PERFSTAT               500 NO
/u01/app/oracle/oradata/orcl/example01.dbf         EXAMPLE                100 YES
/u01/disk1/users01.dbf                             USERS                    5 YES
/u01/app/oracle/oradata/orcl/sysaux01.dbf          SYSAUX                 250 YES
/u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1
/u01/disk2/system01.dbf                            SYSTEM                 490 YES
/u01/app/oracle/oradata/orcl/undotbs02.dbf         UNDOTBS2                50 NO
/u01/disk1/pioneer_data.dbf                        PIONEER_DATA             5 YES
/u01/disk2/pioneer_indx.dbf                        PIONEER_INDX             6 NO
/u01/disk3/pioneer_undo.dbf                        PIONEER_UNDO             7 NO

11 rows selected.


SQL> l
  1* select tablespace_name,bytes/1024/1024 "MB" from dba_free_space  where tablespace_name like '%UNDO%'
SQL> /

TABLESPACE_NAME         MB
--------------- ----------
UNDOTBS2             31.75
PIONEER_UNDO        5.6875


由上查询可知，默认表空间没有空间了，可以切换回滚空间untotbs2为系统的默认表空间。（也可以增加默认表空间untotbs1的大小。）

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo

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


SQL> create table scott.test
  2  (id number(8),
  3  name varchar2(20));

Table created.


问题解决！

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

转载于:http://blog.itpub.net/12778571/viewspace-346816/