Oracle Tips/Questions and Answers:

Question: I had Oracle8i Database and upgraded to Oracle9i with the same tablespace structures. I created my rollback segments and removed the created undo segments tablespace.

Now, when I try to create a table I get the following error message:

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

SQL> create table mytable (mycolumns number) tablespace users;
create table mytable (mycolumns number) 
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

Please help me that why I am getting this error message.

Answer:
On Oracle9i and up, you should use auto rollback segments instead of manully created rollback
segments. What you did, you created the manully rollback segment and forgot to online it. Also, you should use auto rollback segment for non-system tablespace.

To solve this problem do the following:
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
RB1                          SYSTEM                       16384                      OFFLINE


2) alter rollback segment to online status.

SQL> alter rollback segment RB1 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


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 UNDO4iselfschooling 
2 datafile '/oracle/ora90/oradata/orcl/undo4iselfschooling01.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

Also if you check your alert.log file, you will find the following messages:

SMON: enabling tx recovery
***Warning - Executing transaction without active Undo Tablespace
Thu Jun 21 16:37:43 2001
***Warning - Executing transaction without active Undo Tablespace
Thu Jun 21 16:37:47 2001
***Warning - Executing transaction without active Undo Tablespace
Completed: ALTER DATABASE OPEN

which explains that no UNDO tablespace exist, and therefore no UNDO segments.