ORA-1555 errors are very common for long running queries against tables that
are in use.
You don't say how much data is being queried, but it looks like it could be
a lot. When I have had these problems, I normally look at trying to improve
the performance of the query. You should run an explain plan for the query
and look where the time is taken. There should be indexed access one each
sub-query where there is significant data volume. Check that you have a
suitable index on (in particular):
Postcode in cdtaddr
Address_key in cdtaduse
Surname in cdtpersn
Customer_number in ctdcard
Missing indexes (or unused but suitable indexes) will cause tablescans.
Nested tablescans are really bad!
Also have you analyzed the schema/tables used in this query? This can help a
lot.
HTH,
John.
-----Original Message-----
From: shalini
[mailto:oracledba-ezmlmshield-x35876063.[Email address protected]
Sent: 30 June 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
-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.
Oracle LazyDBA home page