Re:Not all rows deleted

From: <dgoulet_at_vicr.com>
Date: Fri, 26 Jan 2001 11:32:12 -0500
Message-Id: <10753.127598@fatcity.com>

OK, As for why the 3 or 4 rows remaining are there I've no real idea based on the code you sent. But on the other hand, why not save your self some grief & do this as a snapshot/materialized view & let Oracle handle it internally?

The statement would be:

create snapshot <name>
refresh complete
start with trunc(sysdate+1)+(<hour>/24)
next trunc(sysdate+1)+(<hour>/24) as
select * from <table_name>@<db>;

Dick Goulet

I hate reinventing the wheel!!!

____________________Reply Separator____________________

Subject: Not all rows deleted
Author: bonnergj_at_songs.sce.com
Date: 1/26/2001 7:36 AM

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 Received on Fri Jan 26 2001 - 10:32:12 CST