Snapshot old

ORA-01555: snapshot too old: rollback segment number 26 with name "_SYSSMU26$" too small

I am getting this error in procedure that procedure is using cursor.proc took 10 hrs to complete.
for one record i have got this error.I am using 10g. Tables have been analyzed properly before running this proc.Please let us know if there is posisible  solution to this.Database is used by many users. WHats main cause for this error??
Who is Participating?
FayyazConnect With a Mentor Commented:
What is the value of your undo_retention parameter?
if it is less than 10 hrs as your procedure is taking to execute then there is always a possiblity that the error will come again.
Either you have to tune your procedure so that it will fit in your undo_retention period or you have to increase your undo_retention period.
Although 10hr undo_retention is not recommended....... But you can increase it to max of 4 or 5 hours.
My recommendation is :  you should tune your procdure so that it will not long time
The following explains the reason for this error:
vamsibatAuthor Commented:
I need solution to reslove.those things i can also do.I am posting so that i will get good solution.I am not expecting those kind of answers.
I will recommend to make the insert/update activity in chunk.
E.g. insert 500 records and issue COMMIT.

There is a parameter retention_interval in the SPFILE that says how long the entries in the UNDO should be kept.
Normally it is very big.
In your case pute there a smaller value like 5 (minutes).
So the UNDO will shrink faster

If you do INSERTs only you can avoid the usage of the rollback segments:

insert /*+APPEND */ into ... select ... from [some other table or view]
But the table must be defined (or altered) to "nologging".

Of course the standart recommendation is to increase the ROLLBACK segment.
Check these parameters and move to automatic UNDO management.

UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION  = 900        # The time undo is retained.
                             # Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE  # Suppress errors when MANUAL undo admin
                             # SQL statements are issued.

Normally your issue needs enlarging the UNDO tablespace.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.