Solved

Snapshot old

Posted on 2009-04-10
6
715 Views
Last Modified: 2013-12-18
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??
0
Comment
Question by:vamsibat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24115793
The following explains the reason for this error:

http://www.ixora.com.au/tips/admin/ora-1555.htm
0
 

Author Comment

by:vamsibat
ID: 24115834
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24116028
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24116107
Check these parameters and move to automatic UNDO management.

UNDO_MANAGEMENT = AUTO       # Default is MANUAL
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.
0
 
LVL 7

Accepted Solution

by:
Fayyaz earned 500 total points
ID: 24122122
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question