Flush a Sql from shared pool

 How to flush a Sql rrom shared pool

1. Get the address and hash_value of the sql_id:

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='Fdx08r6ai732Dd';

ADDRESS HASH_VALUE

---------------------------------- ----------

0000000593W2N264 2515443712

2. Now purge the sql statement 

exec DBMS_SHARED_POOL.PURGE (‘ADDRESS,HASH_VALUE’,’C’);

exec DBMS_SHARED_POOL.PURGE ('0000000593W2N264,2515443712','C');

PL/SQL procedure successfully completed.

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='Fdx08r6ai732Dd';

no rows selected

Altenatively  you can the the script provided by Carlos Sierra.

flush_from_cursor.sql

REM Flushes one cursor out of the shared pool. Works on 11g+

REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.

REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.

REM These scripts are not run by as part of standard database creation.

SPO flush_cursor_&&sql_id..txt;

PRO *** before flush ***

SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

BEGIN

FOR i IN (SELECT address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.')

LOOP

SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');

END LOOP;

END;

/

PRO *** after flush ***

SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

UNDEF sql_id;

SPO OFF;

SQL> @flush_from_cursor.sql

SQL> @flush_from_cursor.sql

Enter value for sql_id: 9df1dk87hwce6

*** before flush ***

old 2: FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1

new 2: FROM gv$sqlarea WHERE sql_id = '9df1dk87hwce6' ORDER BY 1

INST_ID LOADED_VERSIONS INVALIDATIONS ADDRESS HASH_VALUE

---------- --------------- ------------- ---------------- ----------

1 1 1 00000024FC21E608 9422963787

2 1 1 00000027BA545408 9422963787

old 2: FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2

new 2: FROM gv$sql WHERE sql_id = '9df1dk87hwce6' ORDER BY 1, 2

INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I

---------- ------------ --------------- ---------- -

1 0 1169678410 344 Y

2 0 1169678410 344 Y

old 3: FROM gv$sqlarea WHERE sql_id = '&&sql_id.')

new 3: FROM gv$sqlarea WHERE sql_id = '9df1dk87hwce6')

PL/SQL procedure successfully completed.

*** after flush ***

old 2: FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1

new 2: FROM gv$sqlarea WHERE sql_id = '9df1dk87hwce6' ORDER BY 1

INST_ID LOADED_VERSIONS INVALIDATIONS ADDRESS HASH_VALUE

---------- --------------- ------------- ---------------- ----------

1 1 2 00000024FC21E608 9422963787

2 1 1 00000027BA545408 9422963787

old 2: FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2

new 2: FROM gv$sql WHERE sql_id = '9df1dk87hwce6' ORDER BY 1, 2

INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I

---------- ------------ --------------- ---------- -

1 0 1169678410 344 Y

2 0 1169678410 344 Y