I have an insert statement that is hung waiting for a type 4 lock.  I have dug
around a bit and found that a common problem is not enough space for ITL in
block headers.  

However, the table in question is set at Oracle defaults - 10% free 40% used,
initrans 1, maxtrans 255 on a 4K block size.  (Oracle 8.1.7 on AIX 4.3.3)

There are only two sessions in the entire database.   So, I find it hard to
believe that a block would be on the free list (40%) without having enough room
to accomodate what I assume are 2, (only 2), interested transactions. 

So - I pose the following -
1) Is there a way to prove or disprove an ITL problem?  There is no specific
block, row, object in v$session being waited on.  The v$session_wait indicates
an enqueue

2) Can anyone suggest any other scenarios where a transaction which shows an
active insert would be sitting around waiting for a type 4 (S) lock
indefinitely.   

To my knowlege, the blocking sid has nothing more than a large pile of inserts
on the same table that are uncommited.  Also, primary key contention is not an
issue because sequences are used for the primary key.  All foreign keys are
indexed.

Thanks,
Doug
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Doug C
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Reply via email to