Skip to content
Advertisements

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. 🙂

Advertisements
2 Comments Post a comment
  1. Thanks Azar

    January 3, 2013
  2. Mark Ahlstrom #

    Thanks. This helped me pin point my issue… whereas I could not find anything useful on MOS.

    February 29, 2016

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: