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