Library Cache Lock

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Library Cache Lock

Library Cache Lock

2004-06-02 - By Khedr, Waleed

Reply: << 11 12 13 14 15 16 17 18 19 20 >>

Check sessions using the table through v$access

Waleed

-- --Original Message-- --
From: David Sharples [mailto:dsharples@(protected)]
Sent: Wednesday, June 02, 2004 10:08 AM
To: oracle-l@(protected)
Subject: RE: library cache lock

Thanks very much - all I need to do now is go get sys access from the dba, all I have is dba rights at present

Dave

__ __

From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Bobak, Mark
Sent: 02 June 2004 14:58
To: oracle-l@(protected)
Subject: RE: library cache lock

David,

Try the following query to find the blocked sessions and which session is the blocker.

Note that due to the order by, the first session should be the blocker.

-Mark

select /*+ ordered use_nl(lob pn ses) */
decode(lob.kglobtyp, 0, 'NEXT OBJECT ', 1, 'INDEX ', 2, 'TABLE ', 3, 'CLUSTER ',
4, 'VIEW ', 5, 'SYNONYM ', 6, 'SEQUENCE ',
7, 'PROCEDURE ', 8, 'FUNCTION ', 9, 'PACKAGE ',
11, 'PACKAGE BODY ', 12, 'TRIGGER ',
13, 'TYPE ', 14, 'TYPE BODY ',
19, 'TABLE PARTITION ', 20, 'INDEX PARTITION ', 21, 'LOB ',
22, 'LIBRARY ', 23, 'DIRECTORY ', 24, 'QUEUE ',
28, 'JAVA SOURCE ', 29, 'JAVA CLASS ', 30, 'JAVA RESOURCE ',
32, 'INDEXTYPE ', 33, 'OPERATOR ',
34, 'TABLE SUBPARTITION ', 35, 'INDEX SUBPARTITION ',
40, 'LOB PARTITION ', 41, 'LOB SUBPARTITION ',
42, 'MATERIALIZED VIEW ',
43, 'DIMENSION ',
44, 'CONTEXT ', 46, 'RULE SET ', 47, 'RESOURCE PLAN ',
48, 'CONSUMER GROUP ',
51, 'SUBSCRIPTION ', 52, 'LOCATION ',
55, 'XML SCHEMA ', 56, 'JAVA DATA ',
57, 'SECURITY PROFILE ', 59, 'RULE ',
62, 'EVALUATION CONTEXT ',
'UNDEFINED ') object_type,
lob.kglnaobj object_name,
pn.kglpnmod lock_mode_held,
pn.kglpnreq lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
from v$session_wait vsw,
x$kglob lob,
x$kglpn pn,
v$session ses
where vsw.event = 'library cache lock '
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr

order by pn.kglpnmod desc, pn.kglpnreq desc
/

-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On Behalf Of David Sharples
Sent: Wednesday, June 02, 2004 9:40 AM
To: oracle-l@(protected)
Subject: RE: library cache lock

I searched (probably on wrong words) didn 't return anything useful as I could see, can you show me a url?

__ __

From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Khedr, Waleed
Sent: 02 June 2004 14:10
To: oracle-l@(protected)
Subject: RE: library cache lock

The metalink has answers for similar issues.

-- --Original Message-- --
From: David Sharples [mailto:dsharples@(protected)]
Sent: Wednesday, June 02, 2004 8:44 AM
To: oracle-l@(protected)
Subject: library cache lock

Hi, got a strange problem. I am trying to truncate a partition in a table, but it just hangs forever, it is waiting on a library cache lock. Anyone know why and how I can clear it - flushing the shared pool didn 't help and there are no other sessions touching that table.

Thanks

Dave

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML xmlns= "http://www.w3.org/TR/REC-html40 " xmlns:v =
"urn:schemas-microsoft-com:vml " xmlns:o =
"urn:schemas-microsoft-com:office:office " xmlns:w =
"urn:schemas-microsoft-com:office:word " > <HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<TITLE >library cache lock </TITLE >

<META content= "MSHTML 6.00.2800.1400 " name=GENERATOR > <!--[if !mso] >
<STYLE >v\:* {
 BEHAVIOR: url(#default#VML)
}
o\:* {
 BEHAVIOR: url(#default#VML)
}
w\:* {
 BEHAVIOR: url(#default#VML)
}
.shape {
 BEHAVIOR: url(#default#VML)
}
</STYLE >
<![endif]-- >
<STYLE >@(protected) {
 font-family: Tahoma;
}
@(protected) Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
 FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman "
}
LI.MsoNormal {
 FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman "
}
DIV.MsoNormal {
 FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman "
}
A:link {
 COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
 COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
 COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
 COLOR: purple; TEXT-DECORATION: underline
}
P {
 FONT-SIZE: 12pt; MARGIN-LEFT: 0cm; MARGIN-RIGHT: 0cm; FONT-FAMILY: "Times New Roman "; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
SPAN.EmailStyle18 {
 COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal
}
SPAN.EmailStyle19 {
 COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply
}
DIV.Section1 {
 page: Section1
}
</STYLE >
</HEAD >
<BODY lang=EN-US vLink=purple link=blue >
<DIV > Check sessions
using the table through v$access </DIV >
<DIV > </DIV >
<DIV > Waleed </DIV >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > -- --Original Message-- -- From: David Sharples
[mailto:dsharples@(protected)] Sent: Wednesday, June 02,
2004 10:08 AM To: oracle-l@(protected) Subject: RE:
library cache lock </DIV >
<DIV class=Section1 >
 Thanks very much –
all I need to do now is go get sys access from the dba, all I have is dba
rights at present <o:p > </o:p > 
 <o:p > </o:p > 
 Dave <o:p > </o:p > 
 <o:p > </o:p > 
<DIV >
<DIV class=MsoNormal style= "TEXT-ALIGN: center " align=center > 
<HR tabIndex=-1 align=center width= "100% " SIZE=2 >
 </DIV >
 From: 
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Bobak, Mark Sent: 02 June 2004 14:58 To: oracle-l@(protected) Subject: RE: library cache
lock <o:p > </o:p > </DIV >
 <o:p > </o:p > 
<DIV >
 David, <o:p > </o:p > </DIV >
<DIV >
 <o:p > </o:p > </DIV >
<DIV >
 Try the following
query to find the blocked sessions and which session is the
blocker. <o:p > </o:p > </DIV >
<DIV >
 Note that due to the
order by, the first session should be the
blocker. <o:p > </o:p > </DIV >
<DIV >
 <o:p > </o:p > </DIV >
<DIV >
 -Mark <o:p > </o:p > </DIV >
<DIV >
 <o:p > </o:p > </DIV >
<DIV >
 select /*+ ordered
use_nl(lob pn ses) */ 
decode(lob.kglobtyp, 0, 'NEXT OBJECT ', 1, 'INDEX ', 2, 'TABLE ', 3,
'CLUSTER ', 
4, 'VIEW ', 5, 'SYNONYM ', 6,
'SEQUENCE ', 
7, 'PROCEDURE ', 8, 'FUNCTION ', 9,
'PACKAGE ', 
11, 'PACKAGE BODY ', 12,
'TRIGGER ', 
13, 'TYPE ', 14, 'TYPE
BODY ', 
19, 'TABLE PARTITION ', 20, 'INDEX PARTITION ', 21,
'LOB ', 
22, 'LIBRARY ', 23, 'DIRECTORY ', 24,
'QUEUE ', 
28, 'JAVA SOURCE ', 29, 'JAVA CLASS ', 30, 'JAVA
RESOURCE ', 
32, 'INDEXTYPE ', 33,
'OPERATOR ', 
34, 'TABLE SUBPARTITION ', 35, 'INDEX
SUBPARTITION ', 
40, 'LOB PARTITION ', 41, 'LOB
SUBPARTITION ', 
42, 'MATERIALIZED
VIEW ', 
43,
'DIMENSION ', 
44, 'CONTEXT ', 46, 'RULE SET ', 47, 'RESOURCE
PLAN ', 
48, 'CONSUMER
GROUP ', 
51, 'SUBSCRIPTION ', 52,
'LOCATION ', 
55, 'XML SCHEMA ', 56, 'JAVA
DATA ', 
57, 'SECURITY PROFILE ', 59,
'RULE ', 
62, 'EVALUATION
CONTEXT ', 
'UNDEFINED ') object_type, lob.kglnaobj
object_name, pn.kglpnmod
lock_mode_held, pn.kglpnreq
lock_mode_requested, 
ses.sid, 
ses.serial#, ses.username 
from v$session_wait vsw, x$kglob
lob, x$kglpn
pn, v$session ses where
vsw.event = 'library cache lock ' and vsw.p1raw =
lob.kglhdadr and lob.kglhdadr = pn.kglpnhdl 
and pn.kglpnmod != 0 and pn.kglpnuse =
ses.saddr <o:p > </o:p > </DIV >
<DIV >
 order by pn.kglpnmod
desc, pn.kglpnreq desc / <o:p > </o:p > </DIV >
<BLOCKQUOTE style= "MARGIN-TOP: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0cm " >
 -- --Original
Message-- -- From: 
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of David Sharples Sent: Wednesday, June 02, 2004 9:40
AM To: 
oracle-l@(protected) Subject: RE: library cache
lock <o:p > </o:p > 
 I searched
(probably on wrong words) didn’t return anything useful as I could see, can
you show me a url? <o:p > </o:p > 
 <o:p > </o:p > 
<DIV >
<DIV class=MsoNormal style= "TEXT-ALIGN: center " align=center > 
<HR tabIndex=-1 align=center width= "100% " SIZE=2 >
 </DIV >
 From: 
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
 On Behalf Of Khedr,
Waleed Sent: 02 June 2004
14:10 To: 
oracle-l@(protected) Subject: RE: library cache
lock <o:p > </o:p > </DIV >
 <o:p > </o:p > 
<DIV >
 The metalink has answers for similar
issues. <o:p > </o:p > </DIV >
<DIV >
 <o:p > </o:p > </DIV >
<BLOCKQUOTE
style= "MARGIN-TOP: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0cm " > -- --Original
Message-- -- From: David
Sharples [mailto:dsharples@(protected)] Sent: Wednesday, June 02, 2004 8:44
AM To: 
oracle-l@(protected) Subject: library cache
lock <o:p > </o:p > 
 <!-- Converted from text/rtf format -- >Hi,
got a strange problem. I am trying to truncate a partition in a
table, but it just hangs forever, it is waiting on a library cache
lock. Anyone know why and how I can clear it – flushing the shared
pool didn’t help and
there are no other sessions touching that
table. <o:p > </o:p > 
 Thanks <o:p > </o:p > 
 Dave <o:p > </o:p > </BLOCKQUOTE > </BLOCKQUOTE > </DIV > </BLOCKQUOTE > </BODY > </HTML >