RE: Which sessions hold state on which packages

From: Kurt Franke <Kurt-Franke_at_web.de>
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-l

Received on Fri Feb 19 2010 - 11:43:53 CST