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