Search This Blog

Total Pageviews

Wednesday, 31 August 2022

How to purge a Sql from shared pool?



How to purge a Sql from shared pool  ?


define sql_id='62kmmu728kgu6'  ---- Change the sql Id!!!!
select sql_id,ADDRESS, HASH_VALUE from gV$SQLAREA where SQL_Id='&sql_id';



SQL_ID        ADDRESS          HASH_VALUE
------------- ---------------- ----------
62kmmu728kgu6 00000000CE4417E0 3297328966
for purge command!!!!! set linesize 300 col pool_purge for a100 select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','|| HASH_VALUE||''''||','||'''C'''|| ');' pool_purge ,SQL_Id from gV$SQLAREA where SQL_Id='&sql_id'; POOL_PURGE SQL_ID ---------------------------------------------------------------------------------------------------- ------------- exec DBMS_SHARED_POOL.PURGE ('00000000CE4417E0,3297328966','C'); 62kmmu728kgu6 exec DBMS_SHARED_POOL.PURGE ('00000000CCB180F0,3297328966','C'); 62kmmu728kgu6 SQL> exec DBMS_SHARED_POOL.PURGE ('00000000CE4417E0,3297328966','C'); PL/SQL procedure successfully completed. select sql_id,HASH_VALUE, PLAN_HASH_VALUE, CHILD_ADDRESS, CHILD_NUMBER from gv$sql_plan where sql_id='&sql_id';

 select sql_id,address,hash_value,users_executing,sql_text from gv$sqlarea where sql_id='cbr6yy2vwr6s0';

 exec for c in (select address,hash_value,users_executing,sql_text from v$sqlarea where sql_id='cbr6yy2vwr6s0') loop sys.dbms_shared_pool.purge(c.address||','||c.hash_value,'...'); end loop;
 
 
 select sql_id,address,hash_value,users_executing,sql_text from gv$sqlarea where sql_id='cbr6yy2vwr6s0';

No comments:

Oracle DBA

anuj blog Archive