Remove a particular SQL access path from the cache

There are times where a particular SQL statement gets a poor access path and needs to be removed from the cache. Instead of flushing the entire cache, I used this method:

Identify the address and SQL hash value of the problem SQL:

select inst_id, ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '5cjts387mqa82';

Use the address and SQL hash from the above SQL in the below SQL:

exec DBMS_SHARED_POOL.PURGE ('0000000127494510, 2899586625', 'C');

If you need to purge all cached SQLs that access a specific table:

select 'exec DBMS_SHARED_POOL.PURGE(' || '''' || address || ', ' || hash_value || '''' || ',' || '''' || 'C' || '''' || '); '
, ADDRESS, HASH_VALUE 
from V$SQLAREA where SQL_ID in (
select sql_ID 
from v$sqlarea 
where upper(sql_fulltext) like '%table name in upper case%' 
)
;

Note: Run the purge on the instance that the SQL is cached on!