Re: Oracle 8.16 Deadlock

Hi!

I always issue a commit right before any ddl, since issuing a ddl is forcing a commit anyway.
That has solved similar problems for me.

But, is this a routine that disables the foreign key, manipulate some data and then enables the foreign key?
If that's so you will be far better off doing something like this:

Drop the foreign key and recreate it as 'deferrable initially immediate' Now your routine no longer have to issue the ddl, do it like this, f ex using dbms_sql:

set constraint PDGI_ISA_SPECIALTY_FK_CTRY_FK deferred; do_the_other_stuff
set constraint PDGI_ISA_SPECIALTY_FK_CTRY_FK immediate;

A bonus is that you no longer have to block other users' access to the T49PDGI"."PDGI_ISA_SPECIALTY table, because this is pure dml. That is, unless you do other diabling of foreign keys.

Regards,
Njål Ekern
naekern_at_hotmail.com
n.a.ekern_at_usit.uio.no

--
<plasner_at_de.imshealth.com>:
It's just a simple table. The problem happens when the program (PL/SQL,
using the dbms_sql package) tries to do :
ALTER TABLE "T49PDGI"."PDGI_ISA_SPECIALTY" DISABLE CONSTRAINT
"PDGI_ISA_SPECIALTY_FK_CTRY_FK"
The constraint is defined as  FOREIGN KEY (SPEC_CTRY_ID)  REFERENCES
T49PDGI.PDGI_COUNTRIES (CTRY_ID) ;
By the way sometimes the program executes successfully.
Regards Peter