DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, January 8, 2016

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

ERROR:ORA-01552: cannot use system rollback segment for non-system tablespace 'APEX':
***********************************************************************

SOLUTION:
************
1) SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string

2) SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME            STATUS
------------------------------ ----------------
SYSTEM                            ONLINE
_SYSSMU1$                      OFFLINE
_SYSSMU2$                      OFFLINE
_SYSSMU3$                      OFFLINE
_SYSSMU4$                      OFFLINE
_SYSSMU5$                      OFFLINE
_SYSSMU6$                      OFFLINE
_SYSSMU7$                      OFFLINE
_SYSSMU8$                      OFFLINE
_SYSSMU9$                      OFFLINE
_SYSSMU10$                    OFFLINE


3) Change the UNDO MANAGEMENT to AUTO by executing this command.
   SQL> alter system set undo_management=auto scope=spfile;

System altered.

4) Restart the database.

SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 162535656 bytes
Database Buffers 373293056 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL>



5) Check the Auto Management again and make sure its AUTO now.

SQL> show parameter undo_management

NAME                            TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string              AUTO

SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                                    ONLINE
_SYSSMU10_552105004$           ONLINE
_SYSSMU9_2919646627$           ONLINE
_SYSSMU8_1480661775$           ONLINE
_SYSSMU7_3244898802$           ONLINE
_SYSSMU6_4142962160$           ONLINE
_SYSSMU5_1938678686$           ONLINE
_SYSSMU4_138086688$             ONLINE
_SYSSMU3_767986848$             ONLINE
_SYSSMU2_1323822658$           ONLINE
_SYSSMU1_986632303$             ONLINE

No comments: