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