Re: Oracle block locking?

From: Grant Howell <grant_howell_at_hotmail.com>
Date: 21 Feb 2002 12:45:37 -0800
Message-ID: <a84b5eaf.0202211245.58ec1d07@posting.google.com>

It is possible you are experiencing table level locks. This is a known issue and is by design. If you have foreign key references and the referenced column does not have an index it will lock the parent table. Here is a query you can run to check if you are missing indexes:

column column_name format a30
column owner format a15
set linesize 132

select l.owner, l.table_name, l.constraint_name, l.column_name,

         l.position, 'No Index' Problem
from     sys.dba_cons_columns l, sys.dba_constraints c
where    c.constraint_name = l.constraint_name
and       c.owner = l.owner
and      c.constraint_type = 'R'
and      l.owner not in ('SYS','SYSTEM')
and      not exists
         (select   'x'
          from     dba_ind_columns b
          where    b.table_owner = l.owner
          and      b.table_name = l.table_name
          and      b.column_name = l.column_name
          and      b.column_position = l.position)

order by l.owner, l.constraint_name, l.position / Received on Thu Feb 21 2002 - 14:45:37 CST