共有プール（ライブラリ・キャッシュ）にキャッシュしているSQLをクリアしたい時があります。統計が再収集されても、共有SQL領域（共有カーソル）に文の解析ツリーやび実行計画が残っている状況においては、新しい統計情報を採用したプランが作成されません。同様にキャッシュしている状況で、新たなSPM計画ベースラインを登録しこれを採用させたいような状況で活躍します。
使用上の注意点としては、RAC環境で利用する際は「どのインスタンス」を対象にするのか意識して作業してください。
その昔（たぶん11gより前）は共有カーソルをhash_valueとaddressそしてchild_numberから特定していましたが現在はSQL_IDとchild_numberになっています。このプロシージャもSQL_IDが利用できればいいのに、、、と個人的には思っています、、、
１．以下のSQLを流して「実行プロシージャ」を作成する。
v$sqlのaddress、hash_value、child_numberからsys.dbms_shared_pool.purge()プロシージャのパラメータを作成します。
child_number=0の時とそれ以外でパラメータに違いが出る事注意してください。
select s.SQL_ID,s.CHILD_NUMBER
,case when s.CHILD_NUMBER = 0
then
'exec sys.dbms_shared_pool.purge('''||s.ADDRESS||','||s.HASH_VALUE||''',''C'');'
else
'exec sys.dbms_shared_pool.purge('''||s.ADDRESS||','||s.HASH_VALUE||''',''C'''||','||s.CHILD_NUMBER||');'
end "プロシージャ"
from gv$sql s
where s.SQL_ID='2km9mhf1y5pdj'
;
SQL_ID プロシージャ
________________ ______________________________________________________________________
2km9mhf1y5pdj exec sys.dbms_shared_pool.purge('000000009B59C758,2212681137','C');
SQL>２．そして「プロシージャ」を実際に実行
SQL> exec sys.dbms_shared_pool.purge('000000009B59C758,2212681137','C');
PL/SQLプロシージャが正常に完了しました。
SQL>残念にも「PLS-00201: 識別子SYS.DBMS_SHARED_POOLを宣言してください。」と出てしまった場合は、EXECUTE_CATALOG_ROLE ロールを実行スキーマに付与してください。（こちらロールなので再ログインしないと繁栄しませんのでご注意ください）
３．この後v$sqlを見るとキャッシュされていない事がわかります。
SQL> select s.SQL_ID from v$sql s where s.SQL_ID ='2km9mhf1y5pdj';
行が選択されていません
SQL>４．おまけ
今回第２引数（flag パラメータ）で指定している ‘C’ についてですが、Oracleのマニュアルには以下のような記載がありました。
最初の引数がカーソル・アドレスとハッシュ値の場合は、パラメータに’P’、’p’、’Q’、’q’、’R’、’r’、’T’、’t’を除く任意の文字を指定する必要があります。
なんだか良くわからなかったので、興味本位でこのパッケージのソース（仕様部）をみてみると以下のように記載がありましたのでとりあえず’C’ とはしているのですが、’X’でも’Z’でも動作するようです、、、
-- flag
-- This is an optional parameter. If the parameter is not specified,
-- the package assumes that the first parameter is the name of a
-- package/procedure/function and will resolve the name. Otherwise,
-- the parameter is a character string indicating what kind of object
-- to keep the name identifies. The string is case insensitive.
-- The possible values and the kinds of objects they indicate are
-- given in the following table:
-- Value Kind of Object to keep
-- ----- ----------------------
-- P package/procedure/function
-- Q sequence
-- R trigger
-- T type
-- JS java source
-- JC java class
-- JR java resource
-- JD java shared data
-- C cursor
-- If and only if the first argument is a cursor address and hash-value,
-- the flag parameter should be set to 'C' (or 'c').
