- 20 Sep, 2023
- 0 Comments
- 39 Secs Read
How To Flush A Sql Statement From Shared Pool
How To Flush A Sql Statement From Shared Pool ?
If you flush the shared pool, all the statements in cursor will be flushed. So if you want a sql query to do hard parsing, then you can flush the particular sql statement from shared pool.
Follow the below steps :
1. Get the address and hash_value of the sql_id:
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='dmx08r6ayx800'; ADDRESS HASH_VALUE ---------------------------------- ---------- 0000000693E4C268 2515443712
2. Now purge the sql statement
exec DBMS_SHARED_POOL.PURGE (‘ADDRESS,HASH_VALUE’,’C’);
exec DBMS_SHARED_POOL.PURGE ('0000000693E4C268,2515443712','C');
PL/SQL procedure successfully completed.
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='dmx08r6ayx800';
no rows selected