RE: Table Locks

What is the PCTFREE setting for the tables? Hope it is not 0.

Here is the quote form one of my article which explains this behavior

--------------BEGIN QUOTE----------------

Each datablock will have an Interested Transaction List (ITL) that holds the transaction id of that block during the life cycle of the transaction modifying that datablock. A transaction, which modifies a record in the datablock, must get an ITL slot in that datablock. The number of ITL slots in a datablock is defined by the INITRANS (which defaults 1 for data blocks and 2 for index blocks) and MAXTRANS.

While formatting a new block Oracle creates the transaction slots specified by INITRANS parameter. MAXTRANS specifies maximum number of ITLs created for a datablock and it defaults to 255. In practice you don’t need more MAXTRANS unless your AVG_ROW_LENGTH is very small and the segment is frequently updated.

The creation of additional Interested Transaction Lists (ITL) slots is subject to free space in the datablock because each ITL takes approximately 24 bytes of free space in the variable header of that datablock. Initial space reserved by INITRANS cannot be reused for data insertion. But if a datablock is fully packed due to less PCTFREE or PCTFREE=0 and when two transactions are accessing the same block, one has to wait till the transaction commits (or rollbacks). Here row level locks are escalated in to block level locks.

---------------END QUOTE-----------------------------------

And I don't see any reason for row locks becoming table locks unless you have an un indexed foreign key.

Best Regards,
K Gopalakrishnan
Bangalore, INDIA

-----Original Message-----
Mascranghe
Sent: Tuesday, February 12, 2002 4:33 AM To: Multiple recipients of list ORACLE-L

Hi all

We are running on 8.0.5.2.1 database. Once we had a database creash and was restored. After that the users have been experiencing locks. What happens is when one user locks some rows, other users are also getting stuck. But they are not locking the same rows. We are looking at the code to see whether any unusual things are there. One of the programs in the system uses DBMS_ALERT.

How can we find more information - what rows are being locked, and any other relevant info about locks?

Is there any ways in which a row locks turns out to be a table lock ?

Thanks
Alroy

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alroy Mascranghe
  INET: alroy_at_informatics.lk

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).


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.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).