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

今天在测试功能的时候，出现ORA-01552错误，如下：

scott@CNHTM> update emp set sal=sal+1 where empno=7369;

update emp set sal=sal+1 where empno=7369

*

ERROR at line 1:

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

解决过程如下：

确定undo表空间的相关参数

scott@CNHTM> show parameter undo

NAME TYPE VALUE

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

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string

查询undo表空间的名称

scott@CNHTM> select name from v$tablespace;

NAME

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

SYSTEM

UNDOTBS1

SYSAUX

TEMPTS1

TBS_1

USERS

INDX

7 rows selected.

修改数据库参数

1、设置默认undo表空间

2、undo表空间为自动管理

scott@CNHTM> alter system set undo_tablespace=UNDOTBS1 scope=spfile;

System altered.

scott@CNHTM>

scott@CNHTM> alter system set undo_management=auto scope=spfile;

System altered.

重启数据库，测试原来出错的语句

scott@CNHTM> shutdown immediate

ORA-01031: insufficient privileges

scott@CNHTM> conn / as sysdba

Connected.

sys@CNHTM> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@CNHTM> startup

ORACLE instance started.

Total System Global Area 184549376 bytes

Fixed Size 1218388 bytes

Variable Size 134219948 bytes

Database Buffers 41943040 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

sys@CNHTM>

sys@CNHTM> conn scott/tiger

Connected.

scott@CNHTM> update emp set sal=sal+1 where empno=7369;

1 row updated.

--end--