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

1. Please don't post in HTML.
2. It means that you have a rollback segment called SYSTEM, housed in the SYSTEM tablespace, and it's online, but no other rollback segments (wherever they are housed, including SYSTEM) are similarly online.

The reason for the message is that the SYSTEM rollback segment is only ever used for the DML on the data dictionary tables associated with you issuing DML and DDL statements elsewhere (for example, if you 'insert into emp', you may cause emp to acquire a new extent -and that needs to be recorded in the UET$ and FET$ tables (amongst others), so an insert to those tables is handled for you... and the rollback for *those* inserts is handled by the SYSTEM rollback segment).

Because that sort of activity is so important, you cannot use the SYSTEM rollback segment for anything else. So the rollback for your original insert statement (or, in your actual case, an update statement) can't be housed in that rollback segment... so there needs to be another rollback segment somewhere for that job. Any rollback segment not called SYSTEM is a non-system rollback segment. Absolutely NO non-SYSTEM rollback segments are created with the database (despite what some posters here would have you believe), so you have to create all of them for yourself.

Now you can create them anywhere (even in the SYSTEM tablespace if you're mad enough), but the usual practice is to 'create tablespace rbs blah blah blah', and then 'create rollback segment rbs1 ... tablespace rbs'. Repeat as often as necessary (determining *how* often is necessary is an artform in itself, shortly to become obsolete in 9i!), and you end up with a bunch of rollback segments all housed in their own dedicated tablespace. EXCEPT, uniquely amongst segments, just creating a rollback segment is not enough. They have to be brought online. Now, you can do that manually: 'alter rollback segment rbs1 online'. But then if you bounce your Instance, they revert to being offline. So the permanent fix is to edit your init.ora. It has a line that reads 'rollback_segments=.....', and you simply list after the equals sign the names of the rollback segments you want brought online at Instance startup. (So, we'd have one that read rollback_segments=rbs1, rbs2, rbs3, rbs4 and so on).

Et voila! 1552s will be a thing of the past for evermore.

In a busy OLTP environment, you want 1 rollback segment per four concurrent transactions (if you have zillions of transactions, that ratio can come down to something like 1 to 10). In a big, bulky update kind of environment, you tend NOT to have concurrent huge updates, so the number of segments isn't so important.. but each should be huge enough to contain the complete, largest transaction.

Hope that helps
Regards
HJR "Rusty Fadgen" <rfadgen_at_cfl.rr.com> wrote in message news:Bu_K6.196418$fs3.33436034_at_typhoon.tampabay.rr.com... What is:??

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS' And How do I Fix it?

I was trying to update a table and recieved this error Received on Sat May 12 2001 - 02:42:34 CDT