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
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
great solution, it works thanks a lot.
ReplyDeletethanks so much!!!!!
ReplyDeletethanks so much!
ReplyDeletegreat solution.... working for me thnks
ReplyDeleteThanks For the solution.
ReplyDeleteIt helped me alot.
Thank you
ReplyDelete