Purge SQL from shared pool

• Find SQL_ID which one you want to purge SQL.

SELECT   a.cursors, a.sql_id, b.sql_text

  FROM   (  SELECT   COUNT ( * ) AS cursors, ssc.sql_id
              FROM   gv$sql_shared_cursor ssc
          GROUP BY   ssc.sql_id
          ORDER BY   cursors DESC) a, (SELECT   sa.sql_id, sa.sql_text
                                         FROM   gv$sqlarea sa) b
 WHERE   a.sql_id = b.sql_id AND a.cursors > 10;

• Purge related SQL from shared_pool.

DECLARE

   SQ_ADD        VARCHAR2 (100) := '';

   SQ_HASH_VAL   VARCHAR2 (100) := '';

BEGIN

   EXECUTE IMMEDIATE 'select address,hash_value from v$sqlarea where sql_id=''fg9ymndtmypa8'''

      INTO   SQ_ADD, SQ_HASH_VAL;

   DBMS_SHARED_POOL.purge (SQ_ADD || ',' || SQ_HASH_VAL, 'C');

--dbms_output.put_line(SQ_ADD||','||SQ_HASH_VAL);

END;