Showing posts with label Purging. Show all posts
Showing posts with label Purging. Show all posts

Friday, June 25, 2021

How to purge tables from the shared pool

Tables cannot be directly purged from the shared pool using the dbms_shared_pool package. Instead, cursors refering to the table can be purged. To generate statements, use v$sqlarea:
set lines 200
spool purge.sql
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on

select 'exec DBMS_SHARED_POOL.PURGE (''' || ADDRESS ||','|| HASH_VALUE || ''',''C'');'
from V$SQLAREA 
where SQL_ID IN (SELECT SQL_ID 
                 FROM v$sqlarea 
                 WHERE sql_text like 'SELECT * FROM%SBB.ENTITIY_PR%SYS_P5055%');
select 'exit' from dual;
exit
Execute the file "purge.sql" and the cursors refering to the object "SBB.ENTITY_PR" and the partition "SYS_P5055" will be purged. The Oracle 19c documentation for dbms_shared_pool can be found here