Solved

ORA-12801 error signaled in parallel query server P013

Posted on 2004-09-21
8
9,238 Views
Last Modified: 2011-08-18
ORA-12801 error signaled in parallel query server P013
snapshot too old: rollback segment 30 with name "_SYSSMU30$" too small

oracle database version: Oracle9i Enterprise Edition Release 9.0.1.4.0 - 64bit Production
I have add 10 datafiles for undotbs tablespace.(alter tablespace undotbs add datafile'/.../.../undo02.dbf'
size 1500M autoextend on next 100M maxsize 2000M; )
but when I query ,still face those problem.can give me some advice?
I query data from a view,these view have 17,000,000 rows , and I query fromd date20040901-20040920.
thanks.
0
Comment
Question by:ktchanhelp
  • 2
  • 2
8 Comments
 
LVL 8

Expert Comment

by:baonguyen1
ID: 12119164
Hi ktchanhelp,

you can set the undo retention parameter to higher value:

SQL>alter system set undo_retention = ...

This is applied to 9i

Hope this helps
0
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 250 total points
ID: 12119211
ktchanhelp,

This may help you:

 
ORA-01555 Using Automatic Undo Management- Causes and solutions
-----------------------------------------------------------------
 
There are various reasons behind the error ORA-01555. When using plain  
rollback segments as in Oracle 8i and below versions, the reasons and  
possible solutions have been identified in Note:1005107.6 and Note:45895.1
This article shall focus on the Ora-1555 errors received even when using
the AUM(Automatic Undo Management) feature as in Oracle 9i and 10G versions
 
 
Undo_retention :
------------------
 
Ora-1555 error can be caused if Undo_retention parameter is set. Retention is specified  
in units of seconds. This parameter determines the lower threshold value of undo  
retention. The system retains undo for at least the time specified in this  
parameter.
 
The UNDO_RETENTION parameter is only honored if the current undo tablespace has  
enough space. If an active transaction requires undo space and the undo tablespace  
does not have available space, then the system starts reusing unexpired undo space.  
This can cause some queries to fail with ORA-01555(snapshot too old) message.
 
Please note that Oracle does not reuse unexpired extents until and unless  
there are no more free extents available.
 
The Sequence for using extents is as follows:
 
1. A new extent will be allocated from the undo tablespace when the requirement
arises
 
2. If this fails because of no available free extents and we cannot autoextend,
then it tries to steal an expired extent from another undo segment.  
 
3. If it still fails because there are no extents with expired status then it
tries to reuse an unexpired extent from the current undo segment.
 
4. If even that fails, it tries to steal an unexpired extent from another
undo segment.
 
5. If all the above fails, it report an "Out-Of-Space" error.      
 
 
Solution :
 
1.  The UNDO tablespace is too small. Increase the size of the Undo tablespace.
Please note that the undo tablespace should be large enough to store the undo data  
generated by active transaction as well as those ?preserved? to honor the undo  
retention setting.
 
2. Increase the value of the Undo_retention parameter. This is important for  
systems running long queries.  The parameter's value should at least be equal to  
the length of longest running query on a given database instance.
 
This can be determined by querying V$UNDOSTAT view once the database has been
running for a while.
 
SQL> select max(maxquerylen) from v$undostat;  
 
3. Retention Guarantee
 
With Oracle 10G version we have an option to guarantee undo retention. When this  
option is enabled the database never overwrites unexpired undo data that is, undo  
data whose age is less than the undo retention period.  
 
You can enable the guarantee option by specifying the RETENTION GUARANTEE clause  
for the undo tablespace when it is created by either the CREATE DATABASE or CREATE  
UNDO TABLESPACE statement Or at a later period using the ALTER TABLESPACE statement.  
 
Hope this helps
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12120743
Absolutely correct. Find out the maximum time for your queries and increase undo_retention to match it. In earlier versions you had to increase the number of rollback segments to resolve this error
0
 

Author Comment

by:ktchanhelp
ID: 12140406
baonguyen1
thanks,I will check it.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12141433
baonguyen1 u deserve the points....I'll add a request in CS for it....
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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 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

15 Experts available now in Live!

Get 1:1 Help Now