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.


問題解決！