DBMS_CACHE Package

The DBMS_CACHE package facilitates automatic caching for Lake Caches in an Autonomous AI Database instance, including caches created for external tables and for tables exposed through mounted catalogs.

Summary of DBMS_CACHE Subprograms

This table summarizes the subprograms included in the DBMS_CACHE package.

Subprogram	Description
CLEAR Procedure	Clears all Lake Caches for a specified user.
ENABLE_AUTO Procedure	Enables automatic caching globally or for one or more schemas and optionally sets `GLOBAL_CACHE_SIZE` or `GLOBAL_CACHE_PERCENT`.
GET_GLOBAL_PROPERTY Procedure	Retrieves automatic Lake Cache properties.
GET_USER_PROPERTY Procedure	Retrieves automatic Lake Cache properties for a specified user.
REFRESH Procedure	Refreshes all Lake Caches for a specified user.
SET_GLOBAL_PROPERTY Procedure	Specifies the automatic Lake Cache preference for all database users.
SET_USER_PROPERTY Procedure	Specifies the automatic Lake Cache preference for a specified user.

CLEAR Procedure

Use the DBMS_CACHE.CLEAR procedure to drop all Lake Caches for a specified user.

The DBMS_CACHE.CLEAR procedure drops all the Lake 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.

ENABLE_AUTO Procedure

Use the DBMS_CACHE.ENABLE_AUTO procedure to enable automatic caching globally or for one or more schemas and optionally set the global cache quota.

Syntax

DBMS_CACHE.ENABLE_AUTO (
    users             IN   CLOB   DEFAULT NULL,
    global_cache_size IN   NUMBER DEFAULT NULL,
    global_cache_pct  IN   NUMBER DEFAULT NULL
);

Parameters

Parameter	Description
`users`	Specifies a JSON array in CLOB format that contains schema names. If `users` is `NULL`, automatic caching is enabled globally for all users.
`global_cache_size`	Specifies the maximum global cache quota, in bytes, for the database.
`global_cache_pct`	Specifies the global cache quota as a percentage of the maximum database size.

Usage Notes

The procedure enables automatic caching by setting AUTO_CACHE.
If global_cache_size or global_cache_pct is provided, the procedure sets the corresponding global quota property.
If neither global_cache_size nor global_cache_pct is provided, the procedure sets GLOBAL_CACHE_PERCENT to 20.
Use DBMS_CACHE.SET_GLOBAL_PROPERTY or DBMS_CACHE.SET_USER_PROPERTY with AUTO_CACHE = 0 to disable automatic caching.

Examples

Example to enable automatic caching globally:

BEGIN
  DBMS_CACHE.ENABLE_AUTO(
    users            => NULL,
    global_cache_pct => 20);
END;
/

Example to enable automatic caching for selected schemas:

DECLARE
  l_users CLOB := '["HR","SALES"]';
BEGIN
  DBMS_CACHE.ENABLE_AUTO(
    users             => l_users,
    global_cache_size => 21474836480);
END;
/

GET_GLOBAL_PROPERTY Procedure

The DBMS_CACHE.GET_GLOBAL_PROPERTY procedure retrieves the automatic Lake Cache preferences for all users in the database. This procedure is overloaded.

Syntax

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

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`: `AUTO_CACHE`: Enables or disables automatic caching at database scope. The numeric values are `1` and `0`. The default value is `0`. `BEHAVIOR`: Controls automatic cache loading behavior. The string values are `AUTO`, `AGGRESSIVE`, and `COOPERATIVE`. The default value is `AUTO`. `GLOBAL_CACHE_SIZE`: Specifies the global cache quota, in bytes, for the database. A value of -1 indicates an unlimited quota. `-1` means unlimited quota. `GLOBAL_CACHE_PERCENT`: Specifies the global cache quota as a percentage of the maximum database size. `MAX_CACHE_SIZE`: Specifies the default maximum cache size in bytes for schemas without a user-level override. `MAX_CACHE_PERCENT`: Specifies the default maximum cache size as a percentage of the applicable quota base for schemas without a user-level override. `MAX_REFRESH_WINDOW`: Specifies the maximum time window, in seconds, allowed for refreshing lake caches. `AUTO_REFRESH_MODE`: Specifies the scope at which refresh is performed for `AUTO` caches.
`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:

AUTO_CACHE: Enables or disables automatic caching at database scope. The numeric values are 1 and 0. The default value is 0.
BEHAVIOR: Controls automatic cache loading behavior. The string values are AUTO, AGGRESSIVE, and COOPERATIVE. The default value is AUTO.
GLOBAL_CACHE_SIZE: Specifies the global cache quota, in bytes, for the database. A value of -1 indicates an unlimited quota. -1 means unlimited quota.
GLOBAL_CACHE_PERCENT: Specifies the global cache quota as a percentage of the maximum database size.
MAX_CACHE_SIZE: Specifies the default maximum cache size in bytes for schemas without a user-level override.
MAX_CACHE_PERCENT: Specifies the default maximum cache size as a percentage of the applicable quota base for schemas without a user-level override.
MAX_REFRESH_WINDOW: Specifies the maximum time window, in seconds, allowed for refreshing lake caches.
AUTO_REFRESH_MODE: Specifies the scope at which refresh is performed for AUTO caches.

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
  l_auto_cache NUMBER;
BEGIN
  DBMS_CACHE.GET_GLOBAL_PROPERTY(
    property_name      => 'AUTO_CACHE',
    property_value_num => l_auto_cache);
  DBMS_OUTPUT.PUT_LINE('AUTO_CACHE=' || l_auto_cache);
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.
GLOBAL_CACHE_SIZE and GLOBAL_CACHE_PERCENT define quota only. They do not enable automatic caching. Use AUTO_CACHE or DBMS_CACHE.ENABLE_AUTO to enable automatic caching.
If both GLOBAL_CACHE_SIZE and GLOBAL_CACHE_PERCENT are set, the larger value is used. If neither global quota property is explicitly set, GLOBAL_CACHE_PERCENT defaults to 20.
If both MAX_CACHE_SIZE and MAX_CACHE_PERCENT are set, the larger value is used. If neither default quota property is explicitly set, MAX_CACHE_PERCENT defaults to 20.

SET_GLOBAL_PROPERTY Procedure

Use DBMS_CACHE.SET_GLOBAL_PROPERTY to set global automatic-caching properties for the database. This procedure is overloaded.

AUTO_CACHE enables or disables automatic caching at the database scope. GLOBAL_CACHE_SIZE and GLOBAL_CACHE_PERCENT define the global cache quota for all external table caches. MAX_CACHE_SIZE and MAX_CACHE_PERCENT define default schema-level quota preferences for schemas that do not have a user-level override. Setting GLOBAL_CACHE_SIZE, GLOBAL_CACHE_PERCENT, MAX_CACHE_SIZE, or MAX_CACHE_PERCENT does not, by itself, enable automatic caching.

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`: `AUTO_CACHE`: Enables or disables automatic caching at database scope. The valid numeric values are `1` and `0`. The default value is `0`. `BEHAVIOR`: Controls automatic cache loading behavior. The string values are `AUTO`, `AGGRESSIVE`, and `COOPERATIVE`. The default value is `AUTO`. `GLOBAL_CACHE_SIZE`: Specifies the instance-wide global cache quota in bytes. `-1` means unlimited quota. `GLOBAL_CACHE_PERCENT`: Specifies the instance-wide global cache quota as a percentage of the maximum database size. `MAX_CACHE_SIZE`: Specifies the default maximum cache size in bytes for schemas without a user-level override. `MAX_CACHE_PERCENT`: Specifies the default maximum cache size as a percentage of the applicable quota base for schemas without a user-level override. `MAX_REFRESH_WINDOW`: Specifies the maximum time window, in seconds, allowed for refreshing external table caches. `AUTO_REFRESH_MODE`: Specifies the scope at which refresh is performed for `AUTO` caches.
`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:

AUTO_CACHE: Enables or disables automatic caching at database scope. The valid numeric values are 1 and 0. The default value is 0.
BEHAVIOR: Controls automatic cache loading behavior. The string values are AUTO, AGGRESSIVE, and COOPERATIVE. The default value is AUTO.
GLOBAL_CACHE_SIZE: Specifies the instance-wide global cache quota in bytes. -1 means unlimited quota.
GLOBAL_CACHE_PERCENT: Specifies the instance-wide global cache quota as a percentage of the maximum database size.
MAX_CACHE_SIZE: Specifies the default maximum cache size in bytes for schemas without a user-level override.
MAX_CACHE_PERCENT: Specifies the default maximum cache size as a percentage of the applicable quota base for schemas without a user-level override.
MAX_REFRESH_WINDOW: Specifies the maximum time window, in seconds, allowed for refreshing external table caches.
AUTO_REFRESH_MODE: Specifies the scope at which refresh is performed for AUTO caches.

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      => 'AUTO_CACHE',
    property_value_num => 1);
END;
/

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

BEGIN
  DBMS_CACHE.SET_GLOBAL_PROPERTY(
    property_name      => 'MAX_CACHE_PERCENT',
    property_value_num => 25);
END;
/

BEGIN
  DBMS_CACHE.SET_GLOBAL_PROPERTY(
    property_name      => 'BEHAVIOR',
    property_value_str => 'COOPERATIVE');
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.
If both GLOBAL_CACHE_SIZE and GLOBAL_CACHE_PERCENT are set, the larger value is used.
If neither global quota property is explicitly set, GLOBAL_CACHE_PERCENT defaults to 20.
If both MAX_CACHE_SIZE and MAX_CACHE_PERCENT are set, the larger value is used.
If neither default quota property is explicitly set, MAX_CACHE_PERCENT defaults to 20.
If MAX_CACHE_SIZE is set greater than GLOBAL_CACHE_SIZE, the database raises an error.
Manual caches and AUTO caches consume the same quota pool.
The MAX_REFRESH_WINDOW property specifies, in seconds, the maximum time allowed for refreshing Lake 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 takes precedence over the AUTO_REFRESH_MODE property.

See REFRESH Procedure for more information.

REFRESH Procedure

Use the DBMS_CACHE.REFRESH procedure to refresh all AUTO Lake 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`: `AUTO_CACHE`: Enables or disables automatic caching for the specified schema. Valid numeric values are `1` and `0`. `MAX_CACHE_SIZE`: Specifies the schema-level maximum cache size in bytes. `MAX_CACHE_PERCENT`: Specifies the schema-level maximum cache size as a percentage of the applicable quota base. `AUTO_REFRESH_MODE`: Specifies the scope at which refresh is performed for `AUTO` caches.
`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
  l_auto_cache NUMBER;
BEGIN
  DBMS_CACHE.GET_USER_PROPERTY(
    property_name      => 'AUTO_CACHE',
    owner              => 'HR',
    property_value_num => l_auto_cache);
  DBMS_OUTPUT.PUT_LINE('AUTO_CACHE=' || l_auto_cache);
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.
MAX_CACHE_SIZE and MAX_CACHE_PERCENT are schema-level quota controls. They do not enable automatic caching by themselves. Use AUTO_CACHE to enable or disable automatic caching for the specified schema.
If both MAX_CACHE_SIZE and MAX_CACHE_PERCENT are set, the larger value is used. If neither schema-level quota property is explicitly set, MAX_CACHE_PERCENT defaults to 20.

SET_USER_PROPERTY Procedure

Use DBMS_CACHE.SET_USER_PROPERTY to set automatic-caching properties for a specified schema. Use AUTO_CACHE to enable or disable automatic caching for the schema. Use MAX_CACHE_SIZE and MAX_CACHE_PERCENT to set schema-level quota preferences within the global quota model.

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   VARCHAR2,
    owner               IN   VARCHAR2 DEFAULT NULL
);

Parameters

Parameter	Description
`property_name`	Specifies the property name. Following are the valid values for `property_name`: `AUTO_CACHE`: Enables or disables automatic caching for the specified schema. Valid numeric values are `1` and `0`. `MAX_CACHE_SIZE`: Specifies the schema-level maximum cache size in bytes. `MAX_CACHE_PERCENT`: Specifies the schema-level maximum cache size as a percentage of the applicable quota base. `AUTO_REFRESH_MODE`: Specifies the scope at which 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. This is the default value. `CURRENT`: Only existing `AUTO` caches are refreshed. No new caches are added. `NEW`: Only new `AUTO` 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.
`owner`	Specifies the schema name. This parameter is optional and defaults to the current schema.

Examples

Example to enable automatic caching for a schema:

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

Example to set a schema-level cache quota:

BEGIN
  DBMS_CACHE.SET_USER_PROPERTY(
    property_name      => 'MAX_CACHE_SIZE',
    property_value_num => 5368709120,
    owner              => 'SALES');
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.
User-level settings override global settings.
MAX_CACHE_SIZE and MAX_CACHE_PERCENT do not enable automatic caching by themselves.
If both MAX_CACHE_SIZE and MAX_CACHE_PERCENT are set, the larger value is used.
If neither schema-level quota property is explicitly set, MAX_CACHE_PERCENT defaults to 20.
If MAX_CACHE_SIZE is set greater than GLOBAL_CACHE_SIZE, the database raises an error.
Manual policy always overrides automatic behavior.