Oracle | DBMS_SHARED

Oracle DBMS_SHARED_POOL Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose This package makes possible the ability to display the sizes of shared pool objects and mark them for keeping or unkeeping to reduce memory fragmentation.

AUTHID DEFINER

Dependencies

DBMS_GSM_DBADMIN	DBMS_STANDARD	V$SQLAREA
DBMS_OUTPUT	DBMS_UTILITY	X$KGLOB
DBMS_SQLTCB_INTERNAL	V$DB_OBJECT_CACHE

Documented Yes

First Available 10.1

Security Model Owned by SYS with EXECUTE granted to GSMADMIN_INTERNAL and the EXECUTE_CATALOG_ROLE role.

Source {ORACLE_HOME}/rdbms/admin/dbmspool.sql

Subprograms

ABORTED_REQUEST_THRESHOLD	PURGE	UNKEEP
KEEP	PURGE_ROW_CACHE	UNMARKHOT
MARKHOT	SIZES	Related Queries

ABORTED_REQUEST_THRESHOLD

Sets the aborted request threshold for the shared pool dbms_shared_pool.aborted_request_threshold(threshold_size IN NUMBER); -- the range of threshold_size is 5000 to ~2 GB inclusive

exec dbms_shared_pool.aborted_request_threshold(100000000);

KEEP

Pin A Cursor In Memory

Overload 1

dbmsdbms_shared_pool.keep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');

      

        
          
            Flag Values
            Description
          
          
            C
            cursor
          
          
            JC
            java class
          
          
            JD
            java shared data
          
          
            JR
            java resource
          
          
            JS
            java source
          
          
            P
            procedure
          
          
            Q
            sequence
          
          
            R
            trigger
          
          
            T
            type

conn sys@pdbdev as sysdba

      

      GRANT select ON gv_$open_cursor TO uwclass;

      

      conn uwclass/uwclass@pdbdev

      

      -- SQL statement to load cursor into the shared pool

      VARIABLE x REFCURSOR

      

      BEGIN

        OPEN :x for

        SELECT *

        FROM all_all_tables;

      END;

      /

      

      -- determine address and hash value of the SQL statement

      SELECT address, hash_value

      FROM gv$open_cursor

      WHERE sql_text LIKE '%ALL_TABLES%';

      

      -- substitute your query results for mine, below

      exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');

      

      conn sys@pdbdev as sysdba

      

      SELECT owner, name, type

      FROM gv$db_object_cache

      WHERE kept = 'YES'

      AND TYPE = 'CURSOR';

Pin A Package, Procedure Or Function In Memory (this is the default) SELECT owner, name, type FROM gv$db_object_cache WHERE kept = 'YES' AND owner = 'UWCLASS'; CREATE OR REPLACE PROCEDURE testproc IS BEGIN NULL; END testproc; / exec sys.dbms_shared_pool.keep('testproc', 'P'); conn sys@pdbdev as sysdba col owner format a30 SELECT owner, name, type FROM gv$db_object_cache WHERE kept = 'YES' AND owner = 'UWCLASS';

Pin A Sequence In Memory conn sys@pdbdev as sysdba SELECT owner, name, type FROM gv$db_object_cache WHERE kept = 'YES' AND owner = 'UWCLASS'; conn uwclass/uwclass@pdbdev CREATE SEQUENCE seq_test; exec sys.dbms_shared_pool.keep('seq_test', 'Q'); conn sys@pdbdev as sysdba SELECT owner, name, type FROM gv$db_object_cache WHERE kept = 'YES' AND owner = 'UWCLASS';

Pin A Trigger In Memory conn uwclass/uwclass@pdbdev SELECT owner, name, type FROM gv$db_object_cache WHERE kept = 'YES' AND owner = 'UWCLASS'; CREATE TABLE t ( testcol VARCHAR2(20)); CREATE OR REPLACE TRIGGER testtrig BEFORE UPDATE ON t BEGIN NULL; END testtrig; / exec sys.dbms_shared_pool.keep('testtrig', 'R'); conn sys@pdbdev as sysdba SELECT owner, name, type FROM gv$db_object_cache WHERE kept = 'YES' AND owner = 'UWCLASS';

Overload 2 dbms_shared_pool.keep( schema IN VARCHAR2, objname IN VARCHAR2, namespace IN NUMBER, heaps IN NUMBER);

TBD

Overload 3 dbms_shared_pool.keep( hash IN VARCHAR2, namespace IN NUMBER, heaps IN NUMBER);

TBD

MARKHOT

Mark a library cache object as a hot object

Overload 1 dbms_shared_pool.markhot( schema IN VARCHAR2, objname IN VARCHAR2, namespace IN NUMBER DEFAULT 1, -- library cache namespace to search global IN BOOLEAN DEFAULT TRUE); -- If TRUE mark hot on all RAC instances

TBD

Overload 2 dbms_shared_pool.markhot( hash IN VARCHAR2, -- 16-byte hash value for the object namespace IN NUMBER DEFAULT 1, global IN BOOLEAN DEFAULT TRUE);

CREATE OR REPLACE TRIGGER pin_markhot_objects

      AFTER STARTUP ON DATABASE

      BEGIN

        dbms_shared_pool.markhot(hash=>'01630e17906c4f222031266c21b49303',namespace=>0);

        dbms_shared_pool.markhot(hash=>'119df082543f104e29cad00ee793c8aa',namespace=>0);

        dbms_shared_pool.markhot(hash=>'251d24517d18ee7b2154e091b80e64d2',namespace=>0);

        dbms_shared_pool.markhot(hash=>'28104e170c4020b7d6991509b4886443',namespace=>0);

        dbms_shared_pool.markhot(hash=>'3362900d064bc7d9a1812303ea49391e',namespace=>0);

      END;

      /

PURGE

Purge the named object or particular heap(s) of the object

Overload 1 dbms_shared_pool.purge( name IN VARCHAR2, flag IN CHAR DEFAULT 'P', heaps IN NUMBER DEFAULT 1); Note: heaps to purge. e.g if heap 0 and heap 6 are to be purged.1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65. Default is 1 i.e heap 0 which means the whole object will be purged.

conn sys@pdbdev as sysdba

      

      alter session set events '5614566 trace name context forever';

      

      SELECT /* find me */ COUNT(*)

      FROM dba_all_tables t, dba_indexes i

      WHERE t.table_name = i.table_name;

      

      SELECT address, hash_value, sql_text

      FROM v$sqlarea

      WHERE sql_text LIKE '%find me%';

      

      exec dbms_shared_pool.purge('385C52F8,943808449', 5'c');

      

      SELECT address, hash_value, sql_text

      FROM v$sqlarea

      WHERE sql_text LIKE '%find me%';

Overload 2 dbms_shared_pool.purge( schema IN VARCHAR2, objname IN VARCHAR2, namespace IN NUMBER, heaps IN NUMBER, edition_name IN VARCHAR2 DEFAULT NULL);

TBD

Overload 3 dbms_shared_pool.purge( hash IN VARCHAR2, namespace IN NUMBER, heaps IN NUMBER);

TBD

PURGE_ROW_CACHE (new 23ai)

Purges the specified row cache as identified by their rowid dbms_shared_pool.purge_row_cache( cacheid IN NUMBER, hash IN VARCHAR2 NULL);

TBD

SIZES

Shows what is in the Shared Pool larger than a specified size in bytes dbms_shared_pool.sizes(minsize IN NUMBER);

set serveroutput on

      

      exec dbms_shared_pool.sizes(500);

UNKEEP

Unkeep the named object
Overload 1 dbms_shared_pool.unkeep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');

exec dbms_shared_pool.unkeep('UWCLASS.TESTPROC', 'P');

Unkeep an object in the shared pool

Overload 2 dbms_shared_pool.unkeep( schema IN VARCHAR2, objname IN VARCHAR2, namespace IN NUMBER);

TBD

Overload 3 dbms_shared_pool.unkeep( hash IN VARCHAR2, namespace IN NUMBER);

TBD

UNMARKHOT

Unmark a library cache object as a hot object

Overload 1 dbms_shared_pool.unmarkhot( schema IN VARCHAR2, objname IN VARCHAR2, namespace IN NUMBER DEFAULT 1, global IN BOOLEAN DEFAULT TRUE);

TBD

Overload 2 dbms_shared_pool.unmarkhot( hash IN VARCHAR2, namespace IN NUMBER DEFAULT 1, global IN BOOLEAN DEFAULT TRUE);

exec dbms_shared_pool.unmarkhot(hash=>'7eb6e0f357f73998ba9116f63f50f54e', namespace=>0);

Related Queries

Find information on pinned cursors SELECT address, hash_value FROM gv$sqlarea WHERE sql_text LIKE '%<name_from_v$db_object_cache>%';