Pages

Tuesday, March 15, 2011

How can I tell if a procedure/package is running?

Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package.
We have the data dictionary to see who is executing the package that needed a new version. We can kill the session and compile the proc.

Create View av_executing_procs
as
select
      decode(o.kglobtyp,
        7, 'PROCEDURE',
        8, 'FUNCTION',
        9, 'PACKAGE',
        12, 'TRIGGER',
        13, 'CLASS'
     )  "TYPE",
     o.kglnaown  "OWNER",
     o.kglnaobj  "NAME",
     s.indx  "SID",
     s.ksuseser  "SERIAL",
     s.ksuudnam "USERNAME",
     s.ksuseapp "PROGRAM",
     x.app "MODULE",
     x.act "ACTION",
     x.clinfo "CLIENT_INFO",
     'Alter SYSTEM Kill Session '
            || ''''
            || s.indx
            || ','
            || s.ksuseser
            || ''' '
            || 'IMMEDIATE;' Killstmt
   from
     sys.x$kglob  o,
     sys.x$kglpn  p,
     sys.x$ksuse  s,
     sys.x$ksusex x
   where
     o.inst_id = userenv('Instance') and
     p.inst_id = userenv('Instance') and
     s.inst_id = userenv('Instance') and
     x.inst_id = userenv('Instance') and
     p.kglpnhdl = o.kglhdadr and
     s.addr = p.kglpnses and
     s.indx = x.sid and
     s.ksuseser = x.serial and
     o.kglhdpmd = 2 and
     o.kglobtyp in (7, 8, 9, 12, 13)
   order by 1,2,3

No comments:

Post a Comment