RE: Which sessions hold state on which packages

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 */
 @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%;}
@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


Other related posts: