- 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:
1 2 3 4 5 6 | select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='dmx08r6ayx800';ADDRESS HASH_VALUE---------------------------------- ----------0000000693E4C268 2515443712 |
2. Now purge the sql statement
1 2 3 4 5 6 7 8 | 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 |