Flush the SQL id plan from the Shared pool Memory
Shared pool is the SGA area where sql and plsql objects are stored in memory
DBMS_SHARED_POOL.PURGE Procedure is used to flush a SQL plan from Shared pool memory by using the address and hash_value as parameter found in V$SQLAREA.
For Create the DBMS_SHARED_POOL need to execute the script present in RDBMS folder as the DBMSPOOL.SQL script.
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1);
Name: is the name of object to purge from shared pool memory area.
flag: P is default value identify package/procedure/function , T is set for type, R is for trigger and S for sequence
heaps: 0 means whole object to be purged.
schema : username of the object.
objname: Name of the object.
namespace: library cache namespace in which object present.
hash: 16-byte hash value.
Steps for Purging for specific SQL Query from shared pool:
1. Find the SQL id from v$sqlarea view.
select sql_id from v$Sqlarea where sql_Text like 'select * from emp';
2. Find the address and hash value from v$sqlarea after finding sqlid:
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '8ycabihre66ab';
3. Purge the SQL Id with purge procedure:
exec DBMS_SHARED_POOL.PURGE ('000000025AJ44GH0, 453351686', 'C');