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

일단 요약하면 insert할 때 아래 에러가 발생하면 undo tablespace 문제라는건데

undo의 default tablespace를 변경하고 각각의 테이블들의 undo tablespace를 변경해줘야한다.

그 이후 새로 생성되는 테이블은 undo tablespace가 자동 배정되므로 never mind~

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

by Mohamed Azar on October 5, 2010

When i try to insert one row into table, it shows following error

SQL> conn tbsptr/tbsptr;
Connected.
SQL> insert into x values(‘azar’);
insert into x values(‘azar’)
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’

 

This error happened due to your undo tablespace.

If Undo tablespace is missed out in your database —> you need to recreate the undo tablespace

If Undo tablespace size is full

If Undo tablespace datafile offline. —> This is my case.

I have checked my undo tablespace status , it show online.

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
—————————— ———
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1               ONLINE
TEMP                           ONLINE

I just checked  my undo tablespace free space also , it shows 484 MB

SQL> SELECT tablespace_name, sum((bytes/1024)/1024) free FROM DBA_FREE_SPACE group by tablespace_name;

TABLESPACE_NAME                      FREE
—————————— ———-
SYSAUX                            136.125
UNDOTBS1                         484.5625

Finally I forget to see Undo tablespace datafile status,

Now I check

SQL> select file#,status from v$datafile;

     FILE# STATUS
———- ——-
         1 SYSTEM
         2 ONLINE
         3 OFFLINE         

OOPS This is the issues for me, Now I want to alter this tablespace as  online.

 

SQL> alter database datafile ‘C:\APP\MAZAR\ORADATA\OWN\UNDOTBS01.DBF’ online;

Database altered.

SQL> commit;

Commit complete.

SQL> conn tbsptr/tbsptr;
Connected.
SQL> insert into x values(‘azar’);

1 row created.

Now I can able to insert my data into tables.