RE: Which sessions hold state on which packages
Date: Fri, 19 Feb 2010 18:43:53 +0100 (CET)
Message-ID: <16268506.576872.1266601433113.JavaMail.fmail_at_mwmweb035>
Mathias,
I assume you don't do the select excatly at the time your very short package ist active. After it has finished it will no longer occure in v$code_object_in_use
Try the following for a test:
create procedure y
as
begin
dbms_lock.sleep(90);
end y;
/
show error
create procedure z
as
begin
dbms_lock.sleep(150);
end z;
/
show error
create package a
as
procedure action;
end a;
/
create package body a
as
procedure action
as
begin
dbms_lock.sleep(120);
y;
z;
end action;
end a;
/
show error
exec a.action
while a.action is running it is displayed in v$code_object_in_use
while occures in this view grants on it are impossible due to
Normal
0
21
false
false
false
DE
X-NONE
X-NONE
MicrosoftInternetExplorer4
<!--
/* Font Definitions */
_at_font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-unhide:no;
mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; text-autospace:none; font-size:10.0pt; font-family:"Times New Roman","serif"; mso-fareast-font-family:"Times New Roman";} .MsoPapDefault
{mso-style-type:export-only;
margin-bottom:10.0pt; line-height:115%;} _at_page Section1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1
{page:Section1;}
-->
ORA-04021
the initial cause to build this view was to identify the sessions which blocked grants on objects
unlike in the existing view v$access the objects occure in v$code_object_in_use when they are
start to execute.
the procedure y will occure there after approximatly 120 seconds and the procedure z after further 90 seconds.
they will all vanish when the pl/sql environment is leaved which is in this case the same as the end of package a
run.
in opposite v$access would hold entries for y and z as soon as a is started.
if package a would be called from another procedure or package this other object must finish its run unless the objects
vanishes from v$code_object_in_use
regards
kf
>Hi Kurt, Michael, > >interesting question and also I was happy to find an answer. >But unfortunately this did not work for me: > >10.2.0.4 > ># Session 1 > >SQL> create package a > 2 as > 3 a char; > 4 end; > 5 / > >Package created. > >SQL> exec a.a:=1; > >PL/SQL procedure successfully completed. > > ># Session 2 > >SQL> select * from v$code_object_in_use; > >no rows selected > >What I'm doing wrong? > >Mathias
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 19 2010 - 11:43:53 CST