DBMS_SHARED_POOL.PURGE

Remove SQL statement

--
--  Add a few SQL statements to the shared pool:
--
select 1 from dual;
select 2 from dual;
select 3 from dual;

--
--  Verify their existence in the shared pool:
--
select
   sql_text
from
   v$sql
where
   sql_text like 'select _ from dual';

--
--  Remove a given SQL statement from the shared pool.
--  The statement is identified by an address and a hash
--  value. So, determine these values:
--  Because the combination of address and hash_value
--  identifies all child versions of an SQL statement
--  their value is select from v$sqlarea rather than
--  v$sql.
--
column address     new_value  addr
column hash_value  new_value  hash

select
   address,
   hash_value
from
   v$sqlarea
where
   sql_text = 'select 2 from dual';

--
--  Execute purge:  
--
set verify off
begin
--
--  2nd argument 'c' stands for cursor (but it seems that
--  almost any character, except those with a special
--  meaning, can be used...).
--
   sys.dbms_shared_pool.purge('&addr,&hash', 'c');
end;
/

--
--  Indeed, the purged statement is gone:
--
select
   sql_text
from
   v$sql
where
   sql_text like 'select _ from dual';

Procedure

connect / as sysdba
grant execute on dbms_shared_pool to rene;

connect rene/rene

create or replace procedure rene.remove_sql_stmt_in_shared_pool(stmt varchar2)
   authid current_user
is
   a  raw(8);
   h  number;
begin

   select
      address, hash_value
   into
      a      , h
   from
      v$sqlarea
   where
      sql_text = stmt;

   sys.dbms_shared_pool.purge(a || ',' || h, 'c');

   dbms_output.put_line('statement is purged');

exception when others then

   dbms_output.put_line(sqlerrm);

end remove_sql_stmt_in_shared_pool;
/

See also