Home > Scripts > Database Config > ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP’

ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP’

By Robert Jackson | 0 Comments

The Problem

While attempting to gather statistics within my schema, I received the following error:

ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP’

The Cause

I was unsure what the problem was so I wanted to check that I had the basic parameters set correctly.

SQL> show parameter undo

NAME TYPE VALUE
———————————— ————————— ———————
undo_management string MANUAL
undo_retention integer 10800
undo_tablespace string UNDOTBS1

And here is the problem. The undo_management parameter is set to MANUAL but I wanted to be using AUTO. AUTO will use the tablespace specified by undo_tablespace and allows Oracle to automatically manage other parameters related to UNDO management.

The Solution

Issue the following commands in order to change the UNDO_MANAGEMENT parameter to AUTO. This example assumes you are using an SPFILE.

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

System altered.

Elapsed: 00:00:00.01

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2067144 bytes
Variable Size             687867192 bytes
Database Buffers         1442840576 bytes
Redo Buffers               14708736 bytes

Database mounted.
Database opened.

SQL> show parameter undo

NAME TYPE VALUE
———————————— —————— ———————–
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1