Oracle Database Discussions

Hi,

I am using oracle 11g, I am getting an error while doing any DML operations

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

I have done all the things, but no result

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU1_2728543704$           OFFLINE
_SYSSMU2_2139194458$           OFFLINE
_SYSSMU3_3043798095$           OFFLINE
_SYSSMU4_3572839188$           OFFLINE
_SYSSMU5_175534162$            OFFLINE
_SYSSMU6_3369690695$           OFFLINE
_SYSSMU7_731425229$            OFFLINE
_SYSSMU8_3335243932$           OFFLINE
_SYSSMU9_3651921642$           OFFLINE
_SYSSMU10_1127018853$          OFFLINE

11 rows selected.

SQL> update scott.emp set ename='SMM' where empno=7902;
update scott.emp set ename='SMM' where empno=7902
             *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'


SQL> select name,status form v$datafile;
select name,status form v$datafile
                        *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select name,status from v$datafile;

NAME
--------------------------------------------------------------------------------
STATUS
-------
D:\ORACLE\ORADATA\GOLDEN\SYSTEM01.DBF
SYSTEM

D:\ORACLE\ORADATA\GOLDEN\SYSAUX01.DBF
ONLINE

D:\ORACLE\ORADATA\GOLDEN\UNDOTBS01.DBF
OFFLINE


NAME
--------------------------------------------------------------------------------
STATUS
-------
D:\ORACLE\ORADATA\GOLDEN\USERS01.DBF
ONLINE

D:\ORACLE\ORADATA\GOLDEN\GGS_DATA01.DBF
ONLINE

D:\ORACLE\ORADATA\GOLDEN\UNDOTBS02.DBF
ONLINE


6 rows selected.

SQL> create undo tablespace undotbs03
  2  datafile 'D:\ORACLE\ORADATA\GOLDEN\UNDOTBS03.DBF' size 20m;
create undo tablespace undotbs03
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace
'GGS_DATA'
ORA-06512: at line 999
ORA-01552: cannot use system rollback segment for non-system tablespace
'GGS_DATA'


SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             553648592 bytes
Database Buffers          511705088 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.
SQL> alter rollback segment _SYSSMU1_2728543704$ online;
alter rollback segment _SYSSMU1_2728543704$ online
                       *
ERROR at line 1:
ORA-00911: invalid character


SQL> alter rollback segment to online status.
  2
SQL> alter rollback segment to online status;
alter rollback segment to online status
                       *
ERROR at line 1:
ORA-02245: invalid ROLLBACK SEGMENT name


SQL> create undo tablespace undotbs03
  2  datafile 'D:\ORACLE\ORADATA\GOLDEN\UNDOTBS03.DBF' size 20m;
create undo tablespace undotbs03
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace
'GGS_DATA'
ORA-06512: at line 999
ORA-01552: cannot use system rollback segment for non-system tablespace
'GGS_DATA'


SQL>

Edited by: user00726 on Apr 29, 2013 3:03 AM