Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

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: