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
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> schrieb im Newsbeitrag
news:1010743617.8306.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> The deadlock is a library cache deadlock, not
> a data deadlock, so the problem may be an
> Oracle bug, or may be an unexpected side-effect
> from a pl/sql action.
>
> Consider the following daft procedure:
>
> create or replace procedure daft as
> begin
> execute immediate 'alter procedure daft compile';
> end;
>
> Under Oracle 9, if you execute procedure DAFT,
> then you will find yourself waiting for an exclusive
> library cache pin - which you will not be able to
> acquire because your session is holding the
> procedure with a share library cache pin whilst
> it executes it. After 5 minutes you get a time-out.
>
> Perhaps something similar, but a little more obscure,
> is happening in your case, and 8.1.6 does not
> respond so gracefully.
>
> What type of object is: T49PDGI.PDGI_COUNTRIES ?
> A package, procedure, function, table, IOT,
> partitioned table, partitioned IOT etc. ?
>
> I have seen some very strange events in 8.1.6 when
> you try to do partition maintenance on partitioned
> IOTs - and if you try to execute partition maintenance
> on a partitioned IOT from with a procedure which
> is dependent on that partitioned IOT you might get
> the problem you are seeing.
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Now running 3-day intensive seminars
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
>
> Peter Lasner wrote in message <3c3ea093_2_at_news2.prserv.net>...
> > I can't read this trace. Can you give me some hints?
> >What I really do no understand is that one and the same session is
> blocking,
> >I thought that there have to be two sessions and two tables
> >involved to get a deadlock. Below the complete trace file.
> >Regards Peter
> >
> >Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
> >With the Partitioning option
> >JServer Release 8.1.6.0.0 - Production
> >ORACLE_HOME = /usr/oracle/app/product/8.1.6
> >System name: AIX
> >Node name: r011009e
> >Release: 3
> >Version: 4
> >Machine: 000238544C00
> >Instance name: PPDGI49
> >Redo thread mounted by this instance: 1
> >Oracle process number: 35
> >Unix process pid: 33282, image: oracle_at_r011009e (TNS V1-V3)
> >
> >*** 2002-01-09 23:21:13.432
> >*** SESSION ID:(54.18253) 2002-01-09 23:21:13.203
> >A deadlock among DDL and parse locks is detected.
> >This deadlock is usually due to user errors in
> >the design of an application or from issuing a set
> >of concurrent statements which can cause a deadlock.
> >This should not be reported to Oracle Support.
> >The following information may aid in finding
> >the errors which cause the deadlock:
> >ORA-04020: deadlock detected while trying to lock object
> >T49PDGI.PDGI_COUNTRIES
> >--------------------------------------------------------
> > object waiting waiting blocking blocking
> > handle session lock mode session lock mode
> >-------- -------- -------- ---- -------- -------- ----
> >3d892524 3b643688 3bb2b8dc X 3b643688 38dbcae0 S
> >--------------------------------------------------------
> >---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
> >--------------------------------------------------------
> >------------- WAITING LOCK -------------
> >----------------------------------------
> >SO: 3bb2b8dc, type: 33, owner: 3ba79f10, flag: INIT/-/-/0x00
> >LIBRARY OBJECT LOCK: lock=3bb2b8dc handle=3d892524 request=X
> >call pin=0 session pin=0
> >user=3b643688 session=3b643688 count=0 flags=[00] savepoint=39020
> >LIBRARY OBJECT HANDLE: handle=3d892524
> >name=T49PDGI.PDGI_COUNTRIES
> >hash=60e43856 timestamp=08-09-2001 19:07:44
> >namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
> >kkkk-dddd-llll=0000-0005-0005 lock=S pin=S latch=6
> >lwt=3d89253c[3bb2b8ec,3bb2b8ec] ltm=3d892544[3d892544,3d892544]
> >pwt=3d892554[3d892554,3d892554] ptm=3d8925ac[3d8925ac,3d8925ac]
> >ref=3d89252c[3d74e674,3d865d74]
> > LIBRARY OBJECT: object=3bf098f8
> > type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
> > DATA BLOCKS:
> > data# heap pointer status pins change
> > ----- -------- -------- ------ ---- ------
> > 0 37f5b72c 3754a9b0 I/P/A 0 NONE
> > 2 3bf0998c 3d5fc288 I/P/A 1 NONE
> >------------- BLOCKING LOCK ------------
> >----------------------------------------
> >SO: 38dbcae0, type: 33, owner: 3bb23aac, flag: INIT/-/-/0x00
> >LIBRARY OBJECT LOCK: lock=38dbcae0 handle=3d892524 mode=S
> >call pin=3bb27334 session pin=0
> >user=3b643688 session=3b643688 count=2 flags=PNC/[04] savepoint=709
> >LIBRARY OBJECT HANDLE: handle=3d892524
> >name=T49PDGI.PDGI_COUNTRIES
> >hash=60e43856 timestamp=08-09-2001 19:07:44
> >namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
> >kkkk-dddd-llll=0000-0005-0005 lock=S pin=S latch=6
> >lwt=3d89253c[3bb2b8ec,3bb2b8ec] ltm=3d892544[3d892544,3d892544]
> >pwt=3d892554[3d892554,3d892554] ptm=3d8925ac[3d8925ac,3d8925ac]
> >ref=3d89252c[3d74e674,3d865d74]
> > LIBRARY OBJECT: object=3bf098f8
> > type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
> > DATA BLOCKS:
> > data# heap pointer status pins change
> > ----- -------- -------- ------ ---- ------
> > 0 37f5b72c 3754a9b0 I/P/A 0 NONE
> > 2 3bf0998c 3d5fc288 I/P/A 1 NONE
> >--------------------------------------------------------
> >This lock request was aborted.
> >
>
>
>
Received on Fri Jan 11 2002 - 04:50:37 CST
