08 March 2007

Solution of "ORA-01552: cannot use system rollback segment for non-system tablespace "

Today I have struggled a problem while importing a dump file to oracle 9.2.0.7 database. The problem was about "ORA-01552: cannot use system rollback segment for non-system tablespace". I investigated the problem and find a solution...

SQL> show parameter undo_management;
NAME            TYPE       VALUE
--------------- ---------- ---------
undo_management string     MANUAL


SQL> show parameter undo_tablespace;

NAME            TYPE       VALUE
--------------- ---------- ---------
undo_tablespace string    

SQL> alter system set undo_management=AUTO scope=spfile
System altered.


SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile;
System altered.


SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup

ORACLE instance started.
Total System Global Area 253202360 bytes
Fixed Size 733112 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.

 
SQL> show parameter undo_management;
NAME            TYPE       VALUE
--------------- ---------- ---------
undo_management string     AUTO


SQL> show parameter undo_tablespace;

NAME            TYPE       VALUE
--------------- ---------- ---------
undo_tablespace string     UNDOTBS1

4 comments:

parash prasad said...

great solution, it works thanks a lot.

Unknown said...

thanks so much!!!!!

Unknown said...

thanks so much!

Anonymous said...

great solution.... working for me thnks