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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s