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

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

1) find the status of your rollback segments by using the following query. You will find that your tablespace status is offline. Alter the tablespace to online.

SQL> select segment_name, tablespace_name, initial_extent,status
2 from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT STATUS
--------------            ---------------                --------------           -------
SYSTEM                  SYSTEM                       57344                      ONLINE
ASHOKAN                 SYSTEM                       16384                      OFFLINE


2) alter rollback segment to online status.

SQL> alter rollback segment ASHOKAN online;
Rollback segment altered.

Now, you should be able to create table and manipulate your tables. To the following to see whether your undo rollback segments are MANUAL or AUTO..

SQL> show parameter undo

NAME                                            TYPE       VALUE
------------------------------------ ----------- --------------------
undo_management                           string          MANUAL

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO scope=spfile;

3) If the value of undo_management is AUTO, then do the following.

Create an UNDO tablespace to automatically create UNDO segments set online immediately.

SQL> create undo tablespace UNDOTBS2
2 datafile '/oracle/ora11/oradata/orcl/undotbs01.dbf' size 10M;
Tablespace created.

And remove your Manual UNDO Rollback segments.

SQL> create table mytable (mycolumns number) tablespace users;
Table created.

Now, still you should not have any problem.

Oracle recommends you to use the Automatic Undo for your rollback segments. If the database is set in Automatic Undo Management (AUM) mode - it must have at least one UNDO tablespace.

Notice that when the database is using Automatic Undo Management, but no UNDO tablespace is available at the time the error was received.

You could find out when the database is using Automatic Undo Management by to check the following parameters:

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- --------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean TRUE
undo_tablespace string