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
6 comments:
great solution, it works thanks a lot.
thanks so much!!!!!
thanks so much!
great solution.... working for me thnks
Thanks For the solution.
It helped me alot.
Thank you
Post a Comment