Hi
This problem is caused because the uncommitted data in the rollback or
undo segments, which is needed by Oracle to output data from a single
point in time, has been overwritten while the query is executing.
(This is one of Oracle's data consistency / concurrency rules - that
data is consistent to a single point of time. It does this by looking
at old images of the data in the rollback / undo segments after users
have committed data. These old images are eventually overwritten by new
transactions as the rollback segment "wraps".)
SOLUTIONS:
If you're using Oracle 9i with automatic undo management as opposed to
rollback segments (i.e. undo_management is set to auto in init.ora),
then the best thing is to increase the value of undo_retention - the
number of seconds that committed redo is retained in the undo
tablespace. You may also need to increase the size of the undo
tablespace as well, otherwise it won't solve the problem.
Other solutions (esp. if using traditional rollback segments) include
running the query at a quieter period, or tuning the SQL to work more
quickly and efficiently. Making rollback segments bigger (if using
these as opposed to undo tablespace), or increasing optimal size may
also work.
If this doesn't work (it doesn't always), then create temporary tables,
and select from these - however obviously the picture of data, which is
constantly changing, will be slightly different.
Regards
Howard
-----Original Message-----
From: shalini
[mailto:oracledba-ezmlmshield-x35876063.[Email address protected]
Sent: 30 Jun 2006 05:48
To: LazyDBA Discussion
Subject: Query
hi all,
Following is the query and the error which occurs
SELECT customer_number as customernumber
from cdtcard
where customer_number in(select customer_number as customernumber
from cdtpersn
where customer_number in(SELECT customer_number
from cdtaduse
where address_key
in(SELECT
address_key
from cdtaddr
where postcode like 'DE%' ))
AND gender like 'F' AND title like 'MISS' AND surname like 'Long%' )
AND SCHEME_IDENTIFIER = 1, null)
Failed: ORA-01555: snapshot too old: rollback segment number 2 with name
"_SYSSMU2$" too small.
Please help me out as this is very urgent
Whilst this email has been checked for all known viruses, recipients
should undertake their own virus checking as Xansa will not accept any
liability whatsoever.
This email and any files transmitted with it are confidential and
protected by client privilege. It is solely for the use of the intended
recipient.
Please delete it and notify the sender if you have received it in
error. Unauthorised use is prohibited.
Any opinions expressed in this email are those of the individual and not
necessarily the organisation.
Xansa, Registered Office: 420 Thames Valley Park Drive,
Thames Valley Park, Reading, RG6 1PU, UK.
Registered in England No.1000954.
t +44 (0)8702 416181
w www.xansa.com
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
CARILLION VALUES
Openness - Collaboration - Mutual Dependency - Professional Delivery -Sustainable Profitable Growth - Innovation
**********************************************************************
This e-mail transmission, including any attachments, is confidential to the intended recipient. It may contain privileged and confidential information. If you have received this e-mail in error, please delete it and notify the [Email address protected] You must not disclose its contents to anyone, retain, copy, distribute or take action in reliance upon it.
Carillion may monitor outgoing and incoming e-mails. By replying to this e-mail you give your consent to such monitoring.
Carillion plc: Registered in England No. 3782379 Registered Office: Birch Street Wolverhampton WV1 4HY.
This message has been scanned for viruses by BlackSpider MailControl http://www.blackspider.com/, however, Carillion does not accept any responsibility for viruses and it is your responsibility to scan or otherwise check this e-mail and any attachments.
Oracle LazyDBA home page