Re: HELP!! - ORA-01555: snapshot too old: Rollback Segment number 2 ....."_SYSSMSU5$ too small"

From: Ursula Lee <ursula.lee_at_thales-is.com>
Date: Mon, 21 Jun 2004 18:01:31 +0800
Message-ID: <cb6be0$31i9@imsp212.netvigator.com>

Thank you so much Howard, that document is very useful.

I guess my action will be:
1. Try to increase the size in UNDO Retention, I changed to few second only last time as I encounter similar problem during deletion. Seems like the size was not big enough, I changed the retention to clean up space faster. Perhaps this is wrong.

Any suggestion on how long for retention?

2. If #1 doesn't work, I will recreate the UNDO tablespace... According to your document, I must create a new UNDO tablespace (I will use client tool), then change the configuration to point to the new tablespace.

But I will use the default size, is that correct?

3. If #1 or #2 doesn't work, have to increase the UNDO tablespace size, but by how much?

Appreciate if you provide more input on how big tablespace I should use for #2 and #3 above?

Also, do you think this is related to the insertion problem? I get this error message from query, but not from insertion. But after this error message appears, the program stops inserting into database, hope this is the cause of the problem.

Thanks again for all your help... the document is very well written. Regards,
Ursula

Howard J. Rogers wrote:
> "Ursula Lee" <ursula.lee_at_thales-is.com> wrote in message
> news:cb5eqt$mrn2_at_imsp212.netvigator.com...
>

>>Dear all,
>>
>>(FYI.. Oracle database Oracle 9.2.0.1.0 running on HP-UX11)
>>I currently encounter a database problem that the data cannot continue
>>to load into database.
>>
>>I have 2 big table which have almost 100000 of rows inserting each day.
>>    It has been running fine previously, but after my colleagues reboot
>>the server and restart the database, we encounter a major problem namely
>>"snapshot too old".
>>
>>In fact, I am not sure if it is related the reboot, but here is the
>>error message we encounter when retrieving data from the 2 tables (both
>>from client and from sqlplus).
>>
>>ERROR:
>>ORA-01555:  snapshot too old:  rollback segment number 5 with name
>>"_sysmu5$" too small
>>
>>And when I try to retrieve from another table, I got :
>>
>>ERROR:
>>ORA-01555:  snapshot too old:  rollback segment number 2 with name
>>"_sysmu2$" too small
>>
>>Not sure if this problem will stop the program from inserting into
>>database.    But this is the only hint we found.
>>
>>Question:
>>How to fix the problem?  I try to increase the rollback segment size
>>from Oracle Manager Console, but unable to change the number?
>>Is it related to UNDO tablespace?
>>
>>Appreciate if you give me some hint!!!
>>
>>Regards,
>>Ursula

>
>
> Your undo segment names are "weird". Any time they start with underscore
> characters and end with dollar signs, you know immediately that you're
> dealing with automatic undo, and not manual rollback. Which means you cannot
> create your own rollback segments, drop them, offline them, online them,
> make them bigger, make them smaller ...in fact, you can't do anything to
> them, because that's what "automatic undo" means: it will all be managed
> automatically for you by Oracle.
>
> With automatic undo, your only job is to ensure a sufficiently large undo
> tablespace is provided.
>
> Guess which bit of that job you haven't quite managed to execute properly?!
>
> You'll have to add a new data file into the undo tablespace, or resize the
> existing one(s).
>
> For example, alter tablespace undotbs add datafile
> 'c:\somewhere\filename.dbf' size 100m; (adds another 100MB to the existing
> tablespace in the form of a new datafile).
> Or: alter database datafile 'c:\somewhere\filename.dbf' resize 2000m;
> (causes the specified file to grow until it reaches the 2000M size
> requested).
>
> If you want to read up on automatic undo in a little more depth, you may
> wish to have a look at chapter 10 of my 9i new features ebook, available for
> free download at www.dizwell.com.
>
> Regards
> HJR
>
>
>
>
>
Received on Mon Jun 21 2004 - 05:01:31 CDT