Solved

Snapshot old

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 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

13 Experts available now in Live!

Get 1:1 Help Now