:

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

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

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *