Skip to content

How To Flush A Sql Statement From Shared Pool

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

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: