FAQ
Hi

I am trying to grant execute on dbms_pipe package to user userA. My
session was hanging. So I went to v$session_wait to find why my session is
hanging. My session was waiting for library cache pin. I looked at x$kglpn,
x$kglob and saw that my statement was requesting the library cache pin on
exclusive mode(3) and few sessions were executing PL/SQL blocks with calls
to dbms_pipe.unpack_message (so holding the same library cache pin in
shared mode). My questions is why would 'grant' will request the library
cache pin in exclusive mode. I am not even granting to the same user who is
holding the pin. Any ideas ?

SQL> l
1 SELECT

2 a.KGLPNMOD, a.KGLPNREQ, b.username, c.KGLNAOBJ,
3 c.KGLOBTYP
4 FROM

5 x$kglpn a,
6 v$session b,
7 x$kglob c

8 WHERE

9 a.KGLPNUSE = b.saddr and
10 --upper(c.KGLNAOBJ) like upper('%testpkg%') and
11 a.KGLPNHDL = c.KGLHDADR
12* and c.kglnaobj='DBMS_PIPE'
SQL> /

KGLPNMOD KGLPNREQ USERNAME KGLNAOBJ KGLOBTYP

---------- ---------- -------------------- -------------------- ----------
2 0 TEST_USER3 DBMS_PIPE 9
2 0 TEST_USER3 DBMS_PIPE 11
2 0 TEST_USER3 DBMS_PIPE 9
2 0 TEST_USER3 DBMS_PIPE 11
2 0 TEST_USER3 DBMS_PIPE 9
2 0 TEST_USER3 DBMS_PIPE 11
0 3 SYS DBMS_PIPE 9
2 0 TEST_USER3 DBMS_PIPE 9
2 0 TEST_USER3 DBMS_PIPE 11

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

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

Search Discussions

  • Steve Adams at May 3, 2001 at 12:13 pm
    Hi Riyaj,

    The grant needs to invalidate the cached meta data for DBMS_PIPE in the library
    cache. The X pin is required for the invalidation.

    @ Regards,
    @ Steve Adams
    @ http://www.ixora.com.au/
    @ http://www.christianity.net.au/

    -----Original Message-----
    Sent: Thursday, 3 May 2001 4:11
    To: Multiple recipients of list ORACLE-L

    Hi

    I am trying to grant execute on dbms_pipe package to user userA. My
    session was hanging. So I went to v$session_wait to find why my session is
    hanging. My session was waiting for library cache pin. I looked at x$kglpn,
    x$kglob and saw that my statement was requesting the library cache pin on
    exclusive mode(3) and few sessions were executing PL/SQL blocks with calls
    to dbms_pipe.unpack_message (so holding the same library cache pin in
    shared mode). My questions is why would 'grant' will request the library
    cache pin in exclusive mode. I am not even granting to the same user who is
    holding the pin. Any ideas ?

    SQL> l
    1 SELECT

    2 a.KGLPNMOD, a.KGLPNREQ, b.username, c.KGLNAOBJ,
    3 c.KGLOBTYP
    4 FROM

    5 x$kglpn a,
    6 v$session b,
    7 x$kglob c

    8 WHERE

    9 a.KGLPNUSE = b.saddr and
    10 --upper(c.KGLNAOBJ) like upper('%testpkg%') and
    11 a.KGLPNHDL = c.KGLHDADR
    12* and c.kglnaobj='DBMS_PIPE'
    SQL> /

    KGLPNMOD KGLPNREQ USERNAME KGLNAOBJ KGLOBTYP

    ---------- ---------- -------------------- -------------------- ----------
    2 0 TEST_USER3 DBMS_PIPE 9
    2 0 TEST_USER3 DBMS_PIPE 11
    2 0 TEST_USER3 DBMS_PIPE 9
    2 0 TEST_USER3 DBMS_PIPE 11
    2 0 TEST_USER3 DBMS_PIPE 9
    2 0 TEST_USER3 DBMS_PIPE 11
    0 3 SYS DBMS_PIPE 9
    2 0 TEST_USER3 DBMS_PIPE 9
    2 0 TEST_USER3 DBMS_PIPE 11

    Thanks
    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA
    i2 technologies www.i2.com

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

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Steve Adams
    INET: steve.adams_at_ixora.com.au

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 2, '01 at 5:57p
activeMay 3, '01 at 12:13p
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase