Hi all,
W are using Oracle 9.2.0.6 on SUSE linux 10.
Right now we are getting error on alert log file
ORA-01552: cannot use system rollback segment for non-system tablespace
I had cross checked the same it is howing many of the rollback segemtns offilne
SQL> SELECT segment_name, owner, tablespace_name, segment_id, file_id, status FR
OM dba_rollback_segs where tablespace_name='UNDOTBS2';
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU21$ PUBLIC UNDOTBS2 21
19 OFFLINE
_SYSSMU22$ PUBLIC UNDOTBS2 22
19 OFFLINE
_SYSSMU23$ PUBLIC UNDOTBS2 23
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU24$ PUBLIC UNDOTBS2 24
19 OFFLINE
_SYSSMU25$ PUBLIC UNDOTBS2 25
19 OFFLINE
_SYSSMU26$ PUBLIC UNDOTBS2 26
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU27$ PUBLIC UNDOTBS2 27
19 OFFLINE
_SYSSMU28$ PUBLIC UNDOTBS2 28
19 OFFLINE
_SYSSMU29$ PUBLIC UNDOTBS2 29
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU30$ PUBLIC UNDOTBS2 30
19 OFFLINE
10 rows selected.
SQL> /
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU21$ PUBLIC UNDOTBS2 21
19 OFFLINE
_SYSSMU22$ PUBLIC UNDOTBS2 22
19 OFFLINE
_SYSSMU23$ PUBLIC UNDOTBS2 23
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU24$ PUBLIC UNDOTBS2 24
19 OFFLINE
_SYSSMU25$ PUBLIC UNDOTBS2 25
19 OFFLINE
_SYSSMU26$ PUBLIC UNDOTBS2 26
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU27$ PUBLIC UNDOTBS2 27
19 OFFLINE
_SYSSMU28$ PUBLIC UNDOTBS2 28
19 OFFLINE
_SYSSMU29$ PUBLIC UNDOTBS2 29
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU30$ PUBLIC UNDOTBS2 30
19 OFFLINE
10 rows selected.
and i have onlne all the rollback segments
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU21$" ONLINE;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU22$" online;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU23$" online;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU24$" online;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU25$" online;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU26$" online;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU27$" online;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU28$" online;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU29$" online;
Rollback segment altered.
SQL>
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU30$" online;
Rollback segment altered.
even after online the rollback segments it is still showing offilne
and indo_management is set to auto
SQL> SELECT segment_name, owner, tablespace_name, segment_id, file_id, status FR
OM dba_rollback_segs where tablespace_name='UNDOTBS2';
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU21$ PUBLIC UNDOTBS2 21
19 OFFLINE
_SYSSMU22$ PUBLIC UNDOTBS2 22
19 OFFLINE
_SYSSMU23$ PUBLIC UNDOTBS2 23
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU24$ PUBLIC UNDOTBS2 24
19 OFFLINE
_SYSSMU25$ PUBLIC UNDOTBS2 25
19 OFFLINE
_SYSSMU26$ PUBLIC UNDOTBS2 26
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU27$ PUBLIC UNDOTBS2 27
19 OFFLINE
_SYSSMU28$ PUBLIC UNDOTBS2 28
19 OFFLINE
_SYSSMU29$ PUBLIC UNDOTBS2 29
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU30$ PUBLIC UNDOTBS2 30
19 OFFLINE
10 rows selected.
SQL> /
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU21$ PUBLIC UNDOTBS2 21
19 OFFLINE
_SYSSMU22$ PUBLIC UNDOTBS2 22
19 OFFLINE
_SYSSMU23$ PUBLIC UNDOTBS2 23
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU24$ PUBLIC UNDOTBS2 24
19 OFFLINE
_SYSSMU25$ PUBLIC UNDOTBS2 25
19 OFFLINE
_SYSSMU26$ PUBLIC UNDOTBS2 26
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU27$ PUBLIC UNDOTBS2 27
19 OFFLINE
_SYSSMU28$ PUBLIC UNDOTBS2 28
19 OFFLINE
_SYSSMU29$ PUBLIC UNDOTBS2 29
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU30$ PUBLIC UNDOTBS2 30
19 OFFLINE
10 rows selected.
SQL> CREATE ROLLBACK SEGMENT rbs0
2 TABLESPACE undotbs2
3 STORAGE (INITIAL 100k NEXT 100k MINEXTENTS 2);
Rollback segment created.
SQL> SELECT segment_name, owner, tablespace_name, segment_id, file_id, status FR
OM dba_rollback_segs where tablespace_name='UNDOTBS2';
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU21$ PUBLIC UNDOTBS2 21
19 OFFLINE
_SYSSMU22$ PUBLIC UNDOTBS2 22
19 OFFLINE
_SYSSMU23$ PUBLIC UNDOTBS2 23
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU24$ PUBLIC UNDOTBS2 24
19 OFFLINE
_SYSSMU25$ PUBLIC UNDOTBS2 25
19 OFFLINE
_SYSSMU26$ PUBLIC UNDOTBS2 26
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU27$ PUBLIC UNDOTBS2 27
19 OFFLINE
_SYSSMU28$ PUBLIC UNDOTBS2 28
19 OFFLINE
_SYSSMU29$ PUBLIC UNDOTBS2 29
19 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID
------------------------------ ------ ------------------------------ ----------
FILE_ID STATUS
---------- ----------------
_SYSSMU30$ PUBLIC UNDOTBS2 30
19 OFFLINE
10 rows selected.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean TRUE
undo_tablespace string UNDOTBS2
SQL>
please suggest me how to overcome this problem