Hi everyone,
I setup a job in cron that runs an oracle procedure every night at 3am. The
procedure deletes all the
rows of a table(less than 500 rows) then does an "insert select from a
remote database" to reload the
table. We delete rather than truncate because if the job fails it does a
rollback on the table and although
the data is 24 hours old it's preferred over "no data" until the problem is
corrected and reloaded.
The job has started failing 2 out of 5 times for the past several weeks.
When the "insert select" executes
it's failing with a unique constraint violation. The table has a unique
index on one column, same as the
remote table it's selecting from. The table being loaded only has 3
columns, same as the table it's
selecting from.
Just on a chance, I inserted some code between the delete and insert to
count how many records were
on the table after the delete. I was sure the count would be 0.
DELETE FROM bcc.func_desc_table;
SELECT COUNT(*) INTO v_count FROM bcc.func_desc_table;
IF v_count > 0 THEN
RAISE DELETE_ERROR;
END IF;
INSERT INTO BCC.FUNC_DESC_TABLE
(SELECT FUNWO , FUNCDESC , ACTIVITY
FROM BCC.TABLE20_at_BPCC.SONGS.SCE.COM);
I'm hitting the Raise Delete_Error because the count comes back with either
3 or 4 records still in the
table. This explains the unique constraint violation when the table is
reloaded but why are there records
remaining on the table after the Delete?
Next step, I added "LOCK TABLE bcc.func_desc_table IN EXCLUSIVE MODE
NOWAIT" before the delete.
If the table couldn't be locked I would have gone to my Exception handler
with a "-0054 resource busy" .
Even with the table locked it's still hitting the Raise Delete_Error with 3
records remaining on the table.
There are no synonyms and neither table has primary or foreign keys.
When I come in at 7 or 8am and manually run the job there is never a
problem.
Received on Wed Jan 24 2001 - 11:37:21 CST
