ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’
While update of records for a table, rollback segments cannot be used because of manual undo management
SQL> update tabx set order_priority='L' where order_id > 1000 and order_id < 20000;
update tabx set order_priority='L' where order_id > 1000 and order_id < 20000
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
Undo management is enabled as manual
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
While checking , system tablespace is used for managing rollback segments
SQL> select segment_name, status, tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU11_2177280467$ OFFLINE UNDOTBS1
_SYSSMU10_4058727488$ OFFLINE UNDOTBS1
_SYSSMU9_3739287458$ OFFLINE UNDOTBS1
_SYSSMU8_1462975257$ OFFLINE UNDOTBS1
_SYSSMU7_2435451351$ OFFLINE UNDOTBS1
_SYSSMU6_3167659685$ OFFLINE UNDOTBS1
_SYSSMU5_750802473$ OFFLINE UNDOTBS1
_SYSSMU4_4250244621$ OFFLINE UNDOTBS1
_SYSSMU3_3285411314$ OFFLINE UNDOTBS1
_SYSSMU2_1582804868$ OFFLINE UNDOTBS1
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU1_2326716099$ OFFLINE UNDOTBS1
_SYSSMU23_3870541768$ OFFLINE UNDOTBS2
_SYSSMU22_2097329251$ OFFLINE UNDOTBS2
_SYSSMU21_1389753976$ OFFLINE UNDOTBS2
_SYSSMU20_444064395$ OFFLINE UNDOTBS2
_SYSSMU19_114722945$ OFFLINE UNDOTBS2
_SYSSMU18_628965501$ OFFLINE UNDOTBS2
_SYSSMU17_334771145$ OFFLINE UNDOTBS2
_SYSSMU16_3149941514$ OFFLINE UNDOTBS2
_SYSSMU15_2135202654$ OFFLINE UNDOTBS2
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU14_719063781$ OFFLINE UNDOTBS2
_SYSSMU13_1859141474$ OFFLINE UNDOTBS2
_SYSSMU12_1268224145$ OFFLINE UNDOTBS2
25 rows selected.
Create a rollback segment and assign the tablespace to it
SQL> create rollback segment rbseg tablespace UNDOTBS2;
Rollback segment created.
Make the newly created rollback segment online
SQL> alter rollback segment rbseg online;
Rollback segment altered.
Now we can use this segment for rollback purpose
QL> select segment_name, status, tablespace_name from dba_rollback_segs where segment_name='RBSEG';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
RBSEG ONLINE UNDOTBS2
Now DML transactions can be used for read consistency
SQL> update test.tabx set order_priority='L' where order_id > 1000 and order_id < 20000;
9499 rows updated.
SQL> commit;
Commit complete.