ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’
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. 🙂
DBA 