DBMS_CACHE Package

The DBMS_CACHE package facilitates the automatic caching for external table caches in an Autonomous AI Database instance.

Summary of DBMS_CACHE Subprograms

This table summarizes the subprograms included in the DBMS_CACHE package.

Subprogram	Description
CLEAR Procedure	Clears all external table caches for a specified user.
GET_GLOBAL_PROPERTY Procedure	Retrieves external table caching properties.
GET_USER_PROPERTY Procedure	Retrieves external table caching properties for a specified user.
REFRESH Procedure	Refreshes all external table caches for a specified user.
SET_GLOBAL_PROPERTY Procedure	Specifies the external table caching preference for all database users.
SET_USER_PROPERTY Procedure	Specifies the external table caching preference for a specified user.

CLEAR Procedure

Use the DBMS_CACHE.CLEAR procedure to drop all external table caches for a specified user.

The DBMS_CACHE.CLEAR procedure drops all the external table caches and release the storage space, similar to how the DROP command works on a database table.

Syntax

DBMS_CACHE.CLEAR (
    owner    IN   VARCHAR2
);

Parameters

Parameter	Description
`owner`	Specifies the schema name.

Example

BEGIN
 DBMS_CACHE.CLEAR (
    owner => 'SALES');
END;
/

Usage Note

You must be logged in as the ADMIN user or have EXECUTE privilege on the DBMS_CACHE package to clear a cache.

GET_GLOBAL_PROPERTY Procedure

The DBMS_CACHE.GET_GLOBAL_PROPERTY procedure retrieves the external table caching preferences for all the users in the database. This procedure is overloaded.

Syntax

DBMS_CACHE.GET_GLOBAL_PROPERTY (
    property_name       IN   VARCHAR2,
    property_value_num  OUT  VARCHAR2);

DBMS_CACHE.GET_GLOBAL_PROPERTY (
    property_name       IN   VARCHAR2,
    property_value_str  OUT  VARCHAR2);

Parameters

Parameter Description

Parameter	Description
`property_name`	Specifies the property name. Following are the valid values for `property_name`: `MAX_CACHE_PERCENT` `MAX_CACHE_SIZE` `MAX_REFRESH_WINDOW` `AUTO_REFRESH_MODE`
`property_value_num`	Retrieves a `NUMBER` value for the specified property name.
`property_value_str`	Retrieves a `STRING` value for the specified property name.

property_name

Specifies the property name.

Following are the valid values for property_name:

MAX_CACHE_PERCENT
MAX_CACHE_SIZE
MAX_REFRESH_WINDOW
AUTO_REFRESH_MODE

property_value_num Retrieves a NUMBER value for the specified property name.

property_value_str Retrieves a STRING value for the specified property name.

Example

SET SERVEROUTPUT ON;
DECLARE
   cache_property NUMBER;
BEGIN
   DBMS_CACHE.GET_GLOBAL_PROPERTY (
      property_name  => 'MAX_CACHE_SIZE',
      property_value => cache_property
   );
 DBMS_OUTPUT.PUT_LINE('MAX_CACHE_SIZE = ' || cache_property);
END;
/

Usage Note

You must be logged in as the ADMIN user or have EXECUTE privilege on the DBMS_CACHE package to run this procedure.

SET_GLOBAL_PROPERTY Procedure

Use DBMS_CACHE.SET_GLOBAL_PROPERTY procedure to set AUTO caching properties for all database users, including those that enable automatic caching for the database. This procedure is overloaded.

By default, automatic caching of external tables is disabled. To enable it, set the cache size to a non-zero value using the MAX_CACHE_SIZE or MAX_CACHE_SIZE property of the DBMS_CACHE.SET_GLOBAL_PROPERTY procedure. This enables automatic caching globally for the database.

Syntax

DBMS_CACHE.SET_GLOBAL_PROPERTY (
    property_name      IN   VARCHAR2,
    property_value_num IN   NUMBER);

DBMS_CACHE.SET_GLOBAL_PROPERTY (
    property_name      IN   VARCHAR2,
    property_value_str IN   VARCHAR2);

Parameters

Parameter Description

Parameter	Description
`property_name`	Specifies the property name. Following are the valid values for `property_name`: `MAX_CACHE_PERCENT`: The `MAX_CACHE_PERCENT` property specifies the maximum percentage of a user's assigned storage quota that can be used for external table caches. `MAX_CACHE_SIZE`: The `MAX_CACHE_SIZE` property specifies maximum amount of storage, in bytes, that a user can use for external table caches. `MAX_REFRESH_WINDOW`: The `MAX_REFRESH_WINDOW` in property specifies the maximum time window, in seconds, allowed for refreshing external table caches. `AUTO_REFRESH_MODE`: Specifies the scope at which the refresh is performed for `AUTO` caches. Following are the valid values for `AUTO_REFRESH_MODE`: `ALL`: All existing AUTO caches in the database are refreshed and if needed new caches are created. This is the default value. `CURRENT`: only existing caches are refreshed, no new caches are added. `NEW`: only new caches are created.
`property_value_num`	Provides a `NUMBER` value for the specified property name.
`property_value_str`	Provides a `STRING` value for the specified property name.

property_name

Specifies the property name.

Following are the valid values for property_name:

MAX_CACHE_PERCENT: The MAX_CACHE_PERCENT property specifies the maximum percentage of a user's assigned storage quota that can be used for external table caches.
MAX_CACHE_SIZE: The MAX_CACHE_SIZE property specifies maximum amount of storage, in bytes, that a user can use for external table caches.
MAX_REFRESH_WINDOW: The MAX_REFRESH_WINDOW in property specifies the maximum time window, in seconds, allowed for refreshing external table caches.
AUTO_REFRESH_MODE: Specifies the scope at which the refresh is performed for AUTO caches.
Following are the valid values for AUTO_REFRESH_MODE:
- ALL: All existing AUTO caches in the database are refreshed and if needed new caches are created. This is the default value.
- CURRENT: only existing caches are refreshed, no new caches are added.
- NEW: only new caches are created.

property_value_num Provides a NUMBER value for the specified property name.

property_value_str Provides a STRING value for the specified property name.

Examples

BEGIN
 DBMS_CACHE.SET_GLOBAL_PROPERTY (
    property_name       => 'MAX_CACHE_SIZE',
    property_value_num  => 50);
END;
/

BEGIN
 DBMS_CACHE.SET_GLOBAL_PROPERTY (
    property_name       => 'AUTO_REFRESH_MODE',
    property_value_str  => 'NEW');
END;
/

BEGIN
 DBMS_CACHE.SET_GLOBAL_PROPERTY (
    property_name       => 'MAX_REFRESH_WINDOW',
    property_value_num  => 20);
END;
/

Usage Notes

You must be logged in as the ADMIN user or have EXECUTE privilege on the DBMS_CACHE package to run this procedure.
The MAX_REFRESH_WINDOW property specifies, in second, the maximum time allowed for refreshing external table caches. If the refresh exceeds the specified limit, the cache refresh exits normally, and any remaining eligible caches are queued for the next refresh cycle.
When you refresh caches using the DBMS_CACHE.REFRESH procedure, the procedure parameter take precedence over the AUTO_REFRESH_MODE property.

See REFRESH Procedure for more information.

REFRESH Procedure

Use the DBMS_CACHE.REFRESH procedure to refresh all the AUTO external table caches for a specified user.

Syntax

DBMS_CACHE.REFRESH (
      owner          IN VARCHAR2,
      refresh_type   IN VARCHAR2 DEFAULT ALL
);

Parameters

Parameter Description

owner Specifies the schema name.

refresh_type

Specifies the refresh option.

Following are the valid refresh_type values:

ALL: All existing AUTO caches for the specified schema are refreshed and if needed new caches are created for the specified user. This is the default value.
CURRENT: only existing caches are refreshed, no new caches are added for the specified user.
NEW: only new caches are created for the specified user.

This parameter is optional and defaults to ALL.

Example

BEGIN
    DBMS_CACHE.REFRESH (
      owner          => 'SALES',
      refresh_type   => 'CURRENT');
END;
/

Usage Notes

You must be logged in as the ADMIN user or have EXECUTE privilege on the DBMS_CACHE package to run this procedure.
If you do not specify a value for the refresh_type parameter, Oracle perform refresh based on auto_refresh_mode value. The default value for auto_refresh_mode is ALL.
When the MAX_REFRESH_WINDOW property is set, Oracle attempts to perform refresh in the specified window. If the refresh exceeds the specified limit, the cache refresh exits normally, and any remaining eligible caches are queued for the next refresh cycle.
All AUTO caches in the database are managed by using an eviction algorithm similar to Least Recently Used (LRU). When under space pressure, caches that have been accessed least recently are automatically dropped during the refresh process to release the storage space.

GET_USER_PROPERTY Procedure

Use the DBMS_CACHE.GET_USER_PROPERTY procedure to retrieve automatic caching properties for a user.

Syntax

DBMS_CACHE.GET_USER_PROPERTY (
    property_name      IN   VARCHAR2,
    owner              IN   VARCHAR2 DEFAULT NULL,
    property_value_str OUT  VARCHAR2);

DBMS_CACHE.GET_USER_PROPERTY (
    property_name      IN   VARCHAR2,
    owner              IN   VARCHAR2 DEFAULT NULL,
    property_value_num OUT  NUMBER);

Parameters

Parameter	Description
`property_name`	Specifies the property name. Following are the valid values for `property_name`: `MAX_CACHE_PERCENT` `MAX_CACHE_SIZE` `AUTO_REFRESH_MODE`
`owner`	Specifies the schema name. This parameter is optional and defaults to the current schema.
`property_value_str`	Retrieves a `STRING` value for the specified property name.
`property_value_num`	Retrieves a `NUMBER` value for the specified property name.

Example

SET SERVEROUTPUT ON;
DECLARE
   cache_property NUMBER;
BEGIN
   DBMS_CACHE.GET_USER_PROPERTY (
      property_name  => 'MAX_CACHE_SIZE',
      owner          => NULL,
      property_value => cache_property
   );
 DBMS_OUTPUT.PUT_LINE('MAX_CACHE_SIZE = ' || cache_property);
END;
/

Usage Note

You must be logged in as the ADMIN user or have EXECUTE privilege on the DBMS_CACHE package to run this procedure.

SET_USER_PROPERTY Procedure

Use the DBMS_CACHE.SET_USER_PROPERTY procedure to set AUTO caching properties for a specified schema, including those that enable automatic caching for the schema. This procedure is overloaded.

Syntax

DBMS_CACHE.SET_USER_PROPERTY (
    property_name       IN   VARCHAR2,
    property_value_num  IN   NUMBER,
    owner               IN   VARCHAR2 DEFAULT NULL);

DBMS_CACHE.SET_USER_PROPERTY (
    property_name       IN   VARCHAR2,
    property_value_str  IN   NUMBER,
    owner               IN   VARCHAR2 DEFAULT NULL
);

Parameters

Parameter	Description
`property_name`	Specifies the property name. Following are the valid values for `property_name`: `MAX_CACHE_PERCENT` `MAX_CACHE_SIZE` `CONSUMER_GROUP` Following are the valid values for `CONSUMER_GROUP`: `HIGH`: Provides the highest CPU or IO resources with the lowest concurrency. `MEDIUM`: Offers intermediate resources. `LOW`: Provides the lowest CPU or IO resources but allows the highest concurrency. `TP, TPURGENT`: Specialized consumer groups for Transaction Processing (OLTP) workloads, often with higher priority than `HIGH`. `AUTO_REFRESH_MODE`: Specifies the scope at which the refresh is performed for `AUTO` caches. Following are the valid values for `AUTO_REFRESH_MODE`: `ALL`: All existing AUTO caches in the schema are refreshed and if needed new caches are created for the specified user. This is the default value. `CURRENT`: only existing caches are refreshed, no new caches are added for the specified user. `NEW`: only new caches are created for the specified user.
`property_value_num`	Provides a `NUMBER` value for the specified property name.
`property_value_str`	Provides a `STRING` value for the specified property name.
`owner`	Specifies the schema name. This parameter is optional and defaults to the current schema.

Example

BEGIN
 DBMS_CACHE.SET_USER_PROPERTY (
        property_name       => 'MAX_CACHE_SIZE',
        property_value_num  => 50,
        owner               => 'SALES');
END;
/

Usage Note

You must be logged in as the ADMIN user or have EXECUTE privilege on the DBMS_CACHE package to run this procedure.