Problem:

Multiple session was hanging on sqlid that was executing very well one day ago.

Solution:

Flush the single sql id rather than whole shared pool

• Get the address and hash_value of the sql_id:
  select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id=’3jktm6bykn79w’;

• Now purge the sql statement using sys
• exec DBMS_SHARED_POOL.PURGE (‘ADDRESS,HASH_VALUE’,’C’);

exec DBMS_SHARED_POOL.PURGE (‘0700010156DED3F0,4247395644′,’C’);

PL/SQL procedure successfully completed.

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id=’3jktm6bykn79w’;

no rows selected