今天在测试功能的时候，出现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--

[@more@]

ITPub博客

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

最新文章