Table of Contents
This chapter briefly introduces the DBMS_RESULT_CACHE package, and describes how to use the procedures of the package.
DBMS_RESULT_CACHE provides an interface that allow a DBA to control the result cache in the shared memory.
The following are constants defined in the DBMS_RESULT_CACHE package.
STATUS_BYPS
STATUS_BYPS CONSTANT VARCHAR(10) := 'BYPASS'
STATUS_DISA
STATUS_DISA CONSTANT VARCHAR(10) := 'DISABLED'
STATUS_ENAB
STATUS_ENAB CONSTANT VARCHAR(10) := 'ENABLED'
This section describes the procedures provided by the DBMS_RESULT_CACHE package, in alphabetical order.
Configures the bypass mode of the result cache. When the bypass mode is enabled, saved results can no longer be used and new results are not saved. When the bypass mode is disabled, the result cache can be used.
Details about the BYPASS procedure are as follows:
Prototype
DBMS_RESULT_CACHE.BYPASS ( bypass_mode IN BOOLEAN );
Parameter
Parameter | Description |
---|---|
bypass_mode |
|
Example
BEGIN DBMS_RESULT_CACHE.BYPASS(TRUE); DBMS_RESULT_CACHE.FLUSH; END; /
Attempts to delete (flush) all objects that are saved in the result cache, but some objects may fail to be deleted.
Details about the FLUSH function and procedure are as follows:
Prototype
Function
DBMS_RESULT_CACHE.FLUSH ( retain_mem IN BOOLEAN DEFAULT FALSE, retain_stat IN BOOLEAN DEFAULT FALSE ) RETURN BOOLEAN;
Procedure
DBMS_RESULT_CACHE.FLUSH ( retain_mem IN BOOLEAN DEFAULT FALSE, retain_stat IN BOOLEAN DEFAULT FALSE )
Parameter
Parameter | Description |
---|---|
retain_mem |
|
retain_stat |
|
Return Value
Value | Description |
---|---|
BOOLEAN | Returns TRUE when all objects have been deleted. |
Example
BEGIN DBMS_RESULT_CACHE.FLUSH; END; /
Invalidates all results dependent on the user specified object.
Details about the INVALIDATE function and procedure are as follows:
Prototype
object name
DBMS_RESULT_CACHE.INVALIDATE ( owner IN VARCHAR2, name IN VARCHAR2 ) RETURN NUMBER; DBMS_RESULT_CACHE.INVALIDATE ( owner IN VARCHAR2, name IN VARCHAR2 );
data dictionary number
DBMS_RESULT_CACHE.INVALIDATE ( obj_id IN NUMBER ) RETURN NUMBER; DBMS_RESULT_CACHE.INVALIDATE ( obj_id IN NUMBER );
Parameter
Parameter | Description |
---|---|
owner | Schema name. |
name | Object name. |
obj_id | Data dictionary number. |
Return Value
Value | Description |
---|---|
NUMBER | Returns the number of invalidated objects. |
Example
BEGIN
DBMS_RESULT_CACHE.INVALIDATE('TIBEROPRIGHT', 'EMP');
END;
/
Invalidates the user-specified results.
Details about the INVALIDATE_OBJECT function and procedure are as follows:
Prototype
address of a result object
DBMS_RESULT_CACHE.INVALIDATE_OBJECT ( id IN BINARY_INTEGER ) RETURN NUMBER; DBMS_RESULT_CACHE.INVALIDATE_OBJECT ( id IN BINARY_INTEGER );
ID number of a result object
DBMS_RESULT_CACHE.INVALIDATE_OBJECT ( cache_id IN VARCHAR2 ) RETURN NUMBER; DBMS_RESULT_CACHE.INVALIDATE_OBJECT ( cache_id IN VARCHAR2 );
Parameter
Parameter | Description |
---|---|
id | Result object address. |
cache_id | Result object ID. |
Return Value
Value | Description |
---|---|
NUMBER | Returns the number of invalidated objects. |
Example
BEGIN DBMS_RESULT_CACHE.INVALIDATE_OBJECT(3); END; /
Displays the memory usage information of the result cache on the screen. The serveroutput option of tbSQL must be turned on.
Details about the MEMORY_REPORT procedure are as follows:
Prototype
DBMS_RESULT_CACHE.MEMORY_REPORT ( detailed IN BOOLEAN DEFAULT FALSE );
Parameter
Parameter | Description |
---|---|
detailed |
|
Example
BEGIN DBMS_RESULT_CACHE.MEMORY_REPORT; END; /
Returns the current status of the result cache.
Details about the STATUS function are as follows:
Prototype
DBMS_RESULT_CACHE.STATUS RETURN VARCHAR2;
Return Value
Value | Description |
---|---|
VARCHAR2 |
|
Example
BEGIN DBMS_OUTPUT.PUT_LINE(DBMS_RESULT_CACHE.STATUS); END; /