Library cache lock using monitoring tool

From: Mike Schmitt <mschmitt_at_uchicago.edu>
Date: Fri, 17 Dec 2004 15:40:42 -0600
Message-Id: <5.2.0.9.2.20041217145237.02e9ada8@nsit-imap.uchicago.edu>

Hi all,

I am writing about a strange problem I am seeing in a 9204 database. There are only 2 sessions in the database (session A is a load process, the second session was me logged in as the system user using TOAD). Somehow TOAD is locking out session A when it is trying to do an index rebuild in another schema (might lock it for everything, I don't know).

I hardly ever use TOAD, so I am not familiar with what it is trying to do. So now I am trying to figure out what exactly TOAD is doing that would block Session A with a library cache lock. Session A runs fine when I am not logged in with TOAD.

Here is what I am seeing. I can see Toad is putting a lock on the plan_table owned by user_B, but I guess I don't understand how that causes session A to be locked out with a library cache lock.

Session A is trying to rebuild an index in a different schema

alter index user_B.address_key0 rebuild;

^ This hangs with the following

        SID EVENT
---------- ----------------------------------------------------------------
P1TEXT

---

P2TEXT

---

P3TEXT
SECONDS_IN_WAIT

---

---

STATE

---

         22 library cache lock
handle address
lock address
100*mode+namespace
590
WAITING The following is some other info I grabbed. SQL> select * from v$lock where sid in (13,22);

ADDR             KADDR                   SID 
TY        ID1        ID2      LMODE


---------------- ---------------- ---------- -- ---------- ---------- 

----------

    REQUEST      CTIME      BLOCK


---------- ---------- ----------

070000000AD66F18 070000000AD67090         13 
TX     589860       4943          6
          0        990          0

070000000AD21430 070000000AD21458         13 
TM      36148          0          3
          0        990          0

070000000AD214F0 070000000AD21518         22 
TM      34809          0          4
          0        931          0

070000000A2EC1D0 070000000A2EC1F0         22 
DL      34809          0          3
          0        931          0

SQL> select OBJECT_ID,SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_object;

  OBJECT_ID SESSION_ID ORACLE_USERNAME                LOCKED_MODE


---------- ---------- ------------------------------ -----------

      34809         22 SESSION A                                  4
      36148         13 SYSTEM                                   3

SQL> select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects where OBJECT_ID in (34809,36148);
OWNER

---

OBJECT_NAME

---

  OBJECT_ID

---

user_B
PLAN_TABLE
      36148

user_B
ADDRESS
      34809

Is the something else I should be looking at here?

Thanks

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 17 2004 - 15:36:12 CST