ORA -01552 Error

[zircon06]

I'm getting this error when doing insert into the table

oracle ODBC ora ORA-01552 cannot use system rollback segment for non system tablespace user please call support

I'm looking for steps to fix this problem.

Thanks in advance

 

[KenCunningham]

From an oerr ora 01552 command:

01552, 00000, "cannot use system rollback segment for non-system tablespace '%s'
"
// *Cause: Tried to use the system rollback segment for operations involving
// non-system tablespace. If this is a clone database then this will
// happen when attempting any data modification outside of the system
// tablespace. Only the system rollback segment can be online in a
// clone database.
// *Action: Create one or more private/public segment(s), shutdown and then
// startup again. May need to modify the INIT.ORA parameter
// rollback_segments to acquire private rollback segment. If this is
// a clone database being used for tablspace point in time recovery
// then this operation is not allowed.

I want to be good, is that not enough?

 

[Dagon]

Check that you have at least one non-SYSTEM rollback segment created on your database. Unless you do this, you will not be able to create or update any non-system tables.

 

[SantaMufasa]

Zircon,

When this happens to me, it is because all of my non-SYSTEM rollback segments have (for one reason or another) gone OFFLINE. You can check their status with my RBS.sql script, below:

set linesize 200
set pagesize 35
col s format a11 heading "Segment|Name"
col rssize format 999,999,999,999 heading "RBS Size"
col max format 9,999,999,999 heading "Max|Extents"
col xacts heading "Num|Curr|Xacts" format 999
col sgid heading "Seg|Id" format 999
col opt heading "Optimal" format 999,999,999
col nxt heading "Next|Extent" format 99,999,999
col owner heading "Owner" format a6
spool RollbackSegments.txt
select     d.segment_id sgid
   , d.owner
   , d.segment_name s
   , v.rssize
   , v.optsize opt
   , d.next_extent nxt
   , nvl(v.xacts,0) xacts
   , nvl(v.status,'OFFLINE') status
   , d.max_extents max
from dba_rollback_segs d, v$rollstat v
where v.usn(+) = d.segment_id
/
prompt
prompt Wrote spool file "RollbackSegments.txt".
prompt
spool off

Following is output that shows what it will look like if one of your non-SYSTEM rollback segments is offline. (And in your case, if you have non-SYSTEM rollback segments, they will ALL be offline.):

SQL> @rbs
                                                        Num
 Seg        Segment                              Next  Curr             Max
  Id Owner  Name        RBS Size      Optimal  Extent Xacts STATUS  Extents
---- ------ ------- ------------ ------------ ------- ----- ------- -------
   0 SYS    SYSTEM       401,408               57,344     0 ONLINE      505
   2 PUBLIC RBS1     209,707,008  209,715,200             0 ONLINE   32,765
   3 PUBLIC RBS2     269,475,840  209,715,200             0 ONLINE   32,765
   4 PUBLIC RBS3                                          0 OFFLINE  32,765
   5 PUBLIC RBS4     210,755,584  209,715,200             0 ONLINE   32,765
   6 PUBLIC RBS5     210,755,584  209,715,200             0 ONLINE   32,765
   7 PUBLIC RBS6     211,804,160  209,715,200             0 ONLINE   32,765

7 rows selected.

Wrote spool file "RollbackSegments.txt".

Amongst the reasons that your rollback segments can go offline is if they have collectively consumed all of the allocated space that is available for them to use.

Please let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[zircon06]

Hi Mufasa,

You are right this is out put of the database

SGID OWNER S RSSIZE OPT NXT XACTS STATUS MAX

0 SYS SYSTEM 401408 57344 0 ONLINE 505
1 PUBLIC _SYSSMU1$ 0 OFFLINE 32765
2 PUBLIC _SYSSMU2$ 0 OFFLINE 32765
3 PUBLIC _SYSSMU3$ 0 OFFLINE 32765
4 PUBLIC _SYSSMU4$ 0 OFFLINE 32765
5 PUBLIC _SYSSMU5$ 0 OFFLINE 32765
6 PUBLIC _SYSSMU6$ 0 OFFLINE 32765
7 PUBLIC _SYSSMU7$ 0 OFFLINE 32765
8 PUBLIC _SYSSMU8$ 0 OFFLINE 32765
9 PUBLIC _SYSSMU9$ 0 OFFLINE 32765
10 PUBLIC _SYSSMU10$ 0 OFFLINE 32765
What to do in this situation
Thanks a lot

 

[SantaMufasa]

Zircon,

A first thing you can try is simply to bring them back on-line:

ALTER ROLLBACK SEGMENT _SYSSMU1$ ONLINE;

(And let us know the results of that.)

(In your case, you are using "Automatic rollback segments"...I prefer using traditional "Manual rollback segments", so if your Oracle instance hassles you about the above command, since it is what I use to bring my "manual rollback segments" online, then my apologies, and we'll need to either look in the reference manual for the appropriate command or ask another guru here for the correct syntax.)

To isolate why this happened, check in your "alert_<SID>.ora" file (located in the directory to which your instance's "background_dump_dest" parameter points) for any messages that relate to the RBS going offline.

Most importantly, check the available space in the file system(s) that house the files that house your TABLESPACE that houses your rollback segments.

Let us know your findings on all of the above issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[zircon06]

HI mufasa,

I'm getting ORA-00911: invalid character error when issued ALTER ROLLBACK SEGMENT _SYSSMU1$ ONLINE; Any thoughs?

 

[Turkbear]

Hi, I Think names with special characters need to be in quotes:
Try:

ALTER ROLLBACK SEGMENT "_SYSSMU1$" ONLINE

Not sure though, none of ours have any



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Zircon, Turkbear is correct (Sorry to be tardy on the response...I was replying to another thread.) Again, your "non-standard" rollback-segment names derive from their being automatic rollback segments (which Oracle created "for you") versus my preference: manual rollback segments (which I created explicitly).

Let us know progress.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

The format of your rollback segment names indicates that you are using an undo tablespace rather than individual rollback segments. You should not attempt to alter them online yourself. Instead, you need to find out why they are offline. Run a "show parameter undo" in sqlplus to find your undo tablespace name. At a minimum you should then query dba_tablespaces to make sure that the undo tablespace is online.

As an example, on one of my databases:

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

SQL> select tablespace_name,status from dba_tablespaces
  2    where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                STATUS
------------------------------ ---------
UNDOTBS1                       ONLINE

 

[SantaMufasa]

Thanks, KarlUK, for adding the pieces that are lacking from my knowledge since I use the standard rollback segments.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]