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.

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

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

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

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

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

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

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

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.