Re: sessions and locks on procedures

From: Richard Spee <rhpspee_at_wxs.nl>
Date: Wed, 5 Jun 2002 21:40:02 +0200
Message-ID: <adlps0$ed8$1@reader10.wxs.nl>

I believe library cache locks are causing these problems. Clients are problaby having ORA-04020 messages too. I know you want a solution that doesn't require specific object and system-priviliges, but so far this is all i can do. I am not sure if this query is completely correct, but it is supposed to list all views, procedures, triggers, packages, functions that are locked/pinned in the library cache. You can put it in a procedure or view (what ever you like), store it under SYS and grant somebody execute-priv on the procedure or select-priv on the view.

column type format a20
column kglnaobj format a30
column lock_mode format a20
column username format a20
select x$kglob.kglnaobj

,      x$kglob.kglobtyp||decode(x$kglob.kglobtyp,4,' View'
                                                ,7,' Procedure'
                                                ,8,' Function'
                                                ,9,' Package'
                                                ,11,' Package body'
                                                ,12,' Trigger') as type
, dba_kgllock.kgllkmod||decode(dba_kgllock.kgllkmod,1,' null'
                                                ,2,' row share'
                                                ,3,' row exclusive'
                                                ,4,' share table'
                                                ,5,' share row exclusive'
                                                ,6,' exclusive') lock_mode
, dba_kgllock.kgllktype

, v$session.sid
, v$session.username
from dba_kgllock
, x$kglob
, v$session
where dba_kgllock.kgllkhdl = x$kglob.kglhdadr and dba_kgllock.kgllkuse = v$session.saddr and kglobtyp in (4,7,8,9,11,12)
/

"fabienne hadkova" <fh_at_q-bus.com> wrote in message news:adlb45$24sd$1_at_news.imp.ch...
> hello
>
> first, since I am fairly new to oracle, I'ld be glad for links to
> information, so as not to constently bother all of you with
> newbie-questions.
> now, my problem (excuse me if it turns out long, I don't know how to explain
> it fast):
> my company built an application for another company, including some stored
> procedures. the procedures need to compile at every start of the application
> (why this is would be too long to explain).
> we have had the problem many times that, on the side of our client, the
> start-skript for compiling the procedures and running them has been started
> simultaneously by several people, resulting in a lock and bringing the
> application to a time out.
> now, I am writing a script checking all sorts of environment-variables and
> settings which can cause problems, so I need a query which checks the locks
> and finds out who started compiling and when. The problem is that I don't
> know how to do it as a regular user, all I have tried needs sys or
> system-privileges, which we don't have. is there another way to query this?
> I hope I could explain my problem clearly enough and thanks in advance
> fabienne
>
>
>
>
>
Received on Wed Jun 05 2002 - 14:40:02 CDT