invalid ROWID [a little long]

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_at_wp-sa.pl>
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)
--
http://www.freelists.org/webpage/oracle-l