RE: invalid ROWID [a little long]
- From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
- To: rems@xxxxxxxx, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 Jul 2007 12:07:20 -0500
Most likely from the rebuild. I would have to look at the explain plan
to be sure, but most likely its trying to read the index that is being
rebuilt. It's a known issue, you might try to rebuild online...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Remigiusz Sokolowski
Sent: Tuesday, July 31, 2007 8:49 AM
To: 'ORACLE-L'
Subject: Re: invalid ROWID [a little long]
I apologize for lack of proper version info - it is 10.2.0.1 (no
patches) on Solaris10/SPARC, 64-bit
Remigiusz Sokolowski wrote:
> Hi!
>
> while we have got some work-around for the problem I describe below, I
> still wonder where there is a misconception here (and where invalid
> ROWID error could come from) and look for people better informed than
> me (as You :-))
>
> The main concept here is to service large SOAP request which consists
> of many small operations (that is why there are tables soap_requests
> (being parent) and soap_atomic_requests (being children).
>
> There is a loop running at most 3 times. It executes the statement
> SELECT ar.rqa_id, ar.rq_id, [..], ar.pdesc, r.login, r.pass, r.sid
> FROM soap_atomic_requests ar JOIN soap_requests r ON ar.rq_id=r.rq_id
> WHERE ar.status='N' or (ar.status='E' AND ar.attempt<1 AND
> ar.proc_date<sysdate-900/86400)
>
> Then in an inner loop there is a processing of rows fetched from the
> query above. The result of the processing is set in the field status
> success
> UPDATE soap_atomic_requests SET fin_date=sysdate, status='X',
> e_code=:eCode WHERE rqa_id=:blkId
> or failure
> UPDATE soap_atomic_requests SET e_msg=:eMsg, e_code=:eCode,
> proc_date=sysdate, attempt=attempt+1, status='E' WHERE rqa_id=:blkId
> As You may see - there are updates of fields which are provided in
> WHERE clause of the "parent" query
>
> The application is trying to fullfil every subrequest at most 3 times.
> If any of subrequests has failed after 3 tries, whole big request is
> considered as failed.
> This script is being run from the crontab (let's say every 15 minutes)
>
> Another script (which is able to run in parallel and also is run from
> crontab) checks if any of requests-parents is fullfiled in the whole
> (i.e. all its subrequests were performed successfully) and sets
> request-parent status to X. Afterwards the script fetches all
> requests-parents with status X and generates a response.
> Then comes to cleaning
> DELETE FROM soap_atomic_requests a WHERE a.rq_id IN
> (SELECT r.rq_id FROM soap_requests r WHERE r.status='X' AND
> r.resp_path is not null)
> After removing operation script rebuilds indexes on the tables
> involved (as deleted rows can be quite large part of the tables).
> That is all.
>
> However the first script after some time in run catches ORA-1410
> invalid ROWID while fetching a row from
> SELECT ar.rqa_id, ar.rq_id, [..], ar.pdesc, r.login, r.pass, r.sid
> FROM soap_atomic_requests ar JOIN soap_requests r ON ar.rq_id=r.rq_id
> WHERE ar.status='N' or (ar.status='E' AND ar.attempt<1 AND
> ar.proc_date<sysdate-900/86400)
> query.
> The question is why? Is it due to rebuilding the indexes or deletion?
> I assume the ROWIDs of above query don't change (there is no shrinking
> operation performed), though the row movement for the table
> soap_atomic_requests is enabled so in general it is possible - are
> there any row "migrations" in tables behind the scenes?
>
> A workaround is to run the second script after the completion of the
> first one.
>
>
> TIA
> Remigiusz
>
--
---------------------------------------------------------------------
Remigiusz Sokolowski <rems@xxxxxxxx>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v04.x,05.x; Oracle v10.x
Zastrzezenie:
Niniejsza wiadomosc stanowi jedynie wyraz prywatnych pogladow autora
i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa
Wirtualna Polska S.A.
---------------------------------------------------------------------
WIRTUALNA POLSKA SA, ul. Traugutta 115c, 80-226 Gdansk; NIP:
957-07-51-216;
Sad Rejonowy Gdansk-Polnoc KRS 0000068548, kapital zakladowy 62.880.024
zlotych (w calosci wplacony)
--
//www.freelists.org/webpage/oracle-l
------------------------------------------------------------------------------
NOTICE: This electronic mail message and any attached files are confidential.
The information is exclusively for the use of the individual or entity intended
as the recipient. If you are not the intended recipient, any use, copying,
printing, reviewing, retention, disclosure, distribution or forwarding of the
message or any attached file is not authorized and is strictly prohibited. If
you have received this electronic mail message in error, please advise the
sender by reply electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your computer
system. Thank you.
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
