HELP!! - ORA-01555: snapshot too old: Rollback Segment number 2 ....."_SYSSMSU5$ too small"
Ursula Lee
(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
Howard J. Rogers
"Ursula Lee" <ursul...@thales-is.com> wrote in message
news:cb5eqt$mr...@imsp212.netvigator.com...
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
Ursula Lee
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
> 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.
What? I didn't understand a word of that, but how anyone who is told in
black and white "increase the size of your undo tablespace" translates that
into 'increase my setting for undo retention', I honestly can't work out. If
you increase undo *retention* you are only going to make the space
requirements for the undo tablespace worse than they already are.
> Any suggestion on how long for retention?
How long is your longest query? How much use of flashback do you intend to
make? Did you read sections 10.1.3 and 10.1.4??
> 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.
Sorry. You've lost me now. I have no idea what you're talking about, but
whatever it is, it's not what I've written in the ebook, and it's not what
I've posted here. I'll try it again, then:
Increase the size of your existing undo tablespace. Do so by increasing the
size of an existing database file, or by adding a new database file to the
existing tablespace. Alter database datafile '/x/y.dbf' resize 100M is how
you do the former. Alter tablespace undo add datafile '/x/y.dbf' size 100m
is how you do the latter.
HJR
Ursula Lee
Forget to let you know the default size to recreate UNDO table space is
only 200M.