Re: Not all rows deleted

only thing I can think of is that someone, somewhere is reinserting data after the delete and before your insert -- since you say you can run it in the morning.

Couple of things to look at/think about:

this is done via a cron job? what else is running on that machine (or any other that has access to that database and table) at the same time?

when you say "run it manually" -- do you mean type in each step manually or are you running the procedure?

is this userid accessible to any other people in your organization? what grants are on the table? can you revoke them and make sure no one can touch the table while you are working on it?

is there anyway to test to see if the data that is still in the table is new for that day? Did someone put a trigger on the table you are inserting from so that it inserts into the one you are deleting from when someone adds a new row?

Just some of the stuff I'd be looking into to figure this out.

Rachel

>From: bonnergj_at_songs.sce.com
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Not all rows deleted
>Date: Fri, 26 Jan 2001 07:36:11 -0800
>
>Hi everyone,
>Sent this to the list two days ago but got no response. Come on, someone
>has to have some kind of unreasonable, uncomprehensible explanation
>for this.
>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.
>Any ideas, suggestions, theories??
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: bonnergj_at_songs.sce.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).