Re: Error ORA-04031 "Bamima: Grow Bam Buffer" At Run Time

parkerr_at_logica.com (Robin Parker) wrote: >> SNIP <  

>>BTW do you know if DBMS_SHARED_POOL is documented anywhere apart from the Oracle
>>White Paper 
>>that I've seen it on?
>
>I can't recall seeing any other documentation on it, but I think I do remember 
>reading somewhere that these utilities may NOT be included in later versions 
>of Oracle (but I stand to be corrected). Presumably Oracle would then need to 
>provide some other method of pinning which would then be documented in the 
>manuals (but then again knowing Oracle ....     :) )
>
>Robin

All of the DBMS_* packages are documented in their package specification. For example, in the database you could issue the query:

select text
from all_source
where name = 'DBMS_SHARED_POOL'
order by line
/

They are also in text format in the $ORACLE_HOME/rdbms/admin subdirectory.

What you would see for the above query would be:

create or replace package dbms_shared_pool is

• OVERVIEW
  --
  
• This package provides access to the shared pool. This is the
• shared memory area where cursors and PL/SQL objects are stored.

  ---

• PROCEDURES AND FUNCTIONS
  --
  procedure sizes(minsize number);
• Show objects in the shared_pool that are larger than the specified
• size. The name of the object is also given which can be used as
• an argument to either the 'keep' or 'unkeep' calls below. You should
• issue the SQLDBA or SQLPLUS 'set serveroutput on size xxxxx'
• command prior to using this procedure so that the results will
• be displayed.
• Input arguments:
• minsize
• Size, in kilobytes, over which an object must be occupying in the
• shared pool, in order for it to be displayed. procedure keep(name varchar2, flag char DEFAULT 'P');
• Keep an object in the shared pool. Once an object has been keeped in
• the shared pool, it is not subject to aging out of the pool. This
• may be useful for certain semi-frequently used large objects since
• when large objects are brought into the shared pool, a larger
• number of other objects (much more than the size of the object
• being brought in, may need to be aged out in order to create a
• contiguous area large enough.
• WARNING: This procedure may not be supported in the future when
• and if automatic mechanisms are implemented to make this
• unnecessary.
• Input arguments:
• name
• The name of the object to keep. There are two types of objects:
• PL/SQL objects which are specified by name, and SQL cursor
• objects which are specified by a two-part number (indicating
• a location in the shared pool). For example:
• dbms_shared_pool.keep('scott.hispackage')
• will keep package HISPACKAGE, owned by SCOTT. The names for
• PL/SQL objects follows SQL rules for naming objects (i.e.,
• delimited identifiers, multi-byte names, etc. are allowed).
• A cursor can be keeped by
• dbms_shared_pool.keep('0034CDFF, 20348871')
• The complete hexadecimal address must be in the first 8 characters.
• The value for this identifier is the concatonation of the
• 'address' and 'hash_value' columns from the v$sqlarea view. This
• is displayed by the 'sizes' call above.
• Currently 'TABLE' and 'VIEW' objects may not be keeped.
• flag
• This is an optional parameter. If the parameter is not specified,
• the package assumes that the first parameter is the name of a
• package and will resolve the name. It can also be set to 'P' or
• 'p' to fully specify that the input is the name of a package.
• In case the first argument is a cursor address and hash-value, the
• parameter should be set to any character except 'P' or 'p'.
• Exceptions:
• An exception will raised if the named object cannot be found. procedure unkeep(name varchar2, flag char DEFAULT 'P');
• Unkeep the named object.
• WARNING: This procedure may not be supported in the future when
• and if automatic mechanisms are implemented to make this
• unnecessary.
• Input arguments:
• name
• The name of the object to unkeep. See description of the name
• object for the 'keep' procedure.
• Exceptions:
• An exception will raised if the named object cannot be found. end;

Note in particular that some sections, like the following:

  procedure unkeep(name varchar2, flag char DEFAULT 'P');

• Unkeep the named object.
• WARNING: This procedure may not be supported in the future when
• and if automatic mechanisms are implemented to make this
• unnecessary.

State that at some time in the future, this procedure may not be supported and why.

Hope this helps

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Wed Jun 21 1995 - 00:00:00 CEST