Solved

Snapshot old

Posted on 2009-04-10
6
703 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
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 47

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 47

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now