Oraclue

Oracle internals, debugging and undocumented features

Pin anonymous block using dbms_shared_pool package.

How to pin anonymous block  using dbms_shared_pool package ?

If we look at dbms_shared_pool.keep procedure

dbms_shared_pool.keep(name IN VARCHAR2,flag IN CHAR DEFAULT ‘P’);
 
these are the objects that can be pinned 

Flag  Values

C  cursor
JC  java class
JD  java shared data
JR  java resource
JS  java source
P   procedure
Q  sequence
R  trigger
T  type

I can see that view v$sqlarea  has a column command_type and that value 47 is for  anonymous block.So if we have block

DECLARE
/* test */
begin
<code here >;
end;
/

We can pin it using C flag ( cursor )  by supplying  address and hash value
set serveroutput on;
declare
/* DO_NOT_KEEP_ME */
addr varchar2(20);
hash number;
cursor getahv is
    select address,hash_value from v$sqlarea where command_type=47
and sql_text not like ‘%DO_NOT_KEEP_ME%’
and sql_text like ‘%test%’;
begin
open getahv;
loop
   fetch getahv into addr,hash;
   exit when getahv%notfound;
   dbms_output.put_line(addr||to_char(hash));
   dbms_shared_pool.keep(addr||’,’||to_char(hash),’C’);
end loop;
end;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: