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