Library cache lock using monitoring tool
- From: Mike Schmitt <mschmitt@xxxxxxxxxxxx>
- To: Oracle-L-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Dec 2004 15:40:42 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
