Hi,
I got this error(ORA-1555) a few days back and after some searches on
this news group found the reason for it was the flaw in my logic to use
a long running select statement on a table and update the same table
simaltaneously(even though I commit at frequent intervals), since then I
have changed the logic to read from one table and update another temp
table but I still get the same error. I am using Oracle 8.1.5 and jdbc
1.2.
Old Logic
Step 1: Long running query "SELECT emp_id FROM emp_table WHERE ...."
Step 2: Loop thru the result of this cursor and for every emp_id
{
UPDATE emp_table
SET bonus = xyz
WHERE emp_id = current_id in the loop
commitCount++;
if (commitCount == 5000)
{
commit;
commitCount = 0;
}
}
Under this scenario I was getting a ORA-1555 because to
maintain read-consistency of my long running query oracle tries to
access the rollback segment entries created in step 2 and which has been
over-written because of wrap around. To correct this I changed the logic
so that I do not SELECT FROM and UPDATE the same table concurrently .
New Logic
Step 1: Long running query "SELECT emp_id FROM emp_table WHERE ...."
Step 2: Loop thru the result of this cursor and for every emp_id
{
INSERT INTO emp_temp_table VALUES(emp_id);
Here again the commit count mechanism is implemented.
}
Step 3: Long running query "SELECT emp_id FROM emp_temp_table"
Step 4:
Loop thru the result of this cursor and for every emp_id
{
UPDATE emp_table
SET bonus = xyz
WHERE emp_id = current_id in the loop
commitCount++;
if (commitCount == 5000)
{
commit;
commitCount = 0;
}
}
When I tested my new logic I get ORA-1555 when accesing the next emp_id
in the cursor created in step 1.
My quetion is: Why do I get a ORA-1555 when I read from table 'X' and
update table 'Y' and there is no need for the ORACLE to access the
rollback segment entries created by updates to table 'Y'?. I am sure
that there are NO other processes running apart from this batch process.
Thanks for the help.
Regards,
Sridhar
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Mar 21 2000 - 00:00:00 CST
