Solved

Snapshot old

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
any step by steps guide on how to install Oracle 12c on Windows 10 8 174
ER Diagram 3 52
oracle differnce between two timestamps 5 51
Oracle Date 6 39
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

737 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