Oracle Cloud Infrastructure Documentation

DBMS_EXT_TABLE_CACHE Package

The DBMS_EXT_TABLE_CACHE package provides routines to configure and manage caching of frequently accessed data from external tables and from tables exposed through mounted catalogs into Autonomous AI Database.

Summary of DBMS_EXT_TABLE_CACHE Subprograms

This table summarizes the subprograms included in the DBMS_EXT_TABLE_CACHE package.

Subprogram Description
ADD_BY_LIKE Procedure Adds one or more files that match the specified filters into the Lake Cache.
ADD_FILE Procedure Adds a file to the Lake Cache.
ADD_LATEST_FILES Procedure Populates one or more files based on the time interval specified by the SINCE argument and the current timestamp.
ADD_TABLE Procedure Adds files from the specified source table into the Lake Cache. The source table can be either an external table or a table exposed through a mounted catalog.
CLEAR Procedure Clears a Lake Cache.
CREATE_CACHE Procedure Creates a Lake Cache.
DISABLE Procedure Disables a Lake Cache.
DROP_BY_LIKE Procedure Drop files from the Lake Cache based on the specified filters.
DROP_CACHE Procedure Drops a Lake Cache.
DROP_FILE Procedure Removes the specified source file from the Lake Cache.
ENABLE Procedure Enables a previously disabled Lake Cache.
GET_AVAILABLE_QUOTA Function Returns the remaining available cache quota for a schema or for a specific external table cache.
GET_GLOBAL_CACHE_QUOTA Function Returns the resolved global cache quota.
GET_USER_PROPERTY Function Retrieves the caching preference for a Lake Cache.
REFRESH_TABLE Procedure Deletes cached data for any files that no longer exist, and loads the files that are new or newly modified.
RETIRE_FILES Procedure Deletes one or more files from the cache that are older than the specified interval.
SET_USER_PROPERTY Procedure Specifies the caching preference for the Lake Cache.
VALIDATE Procedure Validates a Lake Cache.

ADD_BY_LIKE Procedure

The DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE procedure loads one or more specified source files into the Lake Cache.

Syntax

DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
      owner        IN VARCHAR2,
      table_name   IN VARCHAR2,
      path_filters IN CLOB,
      esc_char     IN VARCHAR2 DEFAULT NULL,
      force        IN BOOLEAN  DEFAULT FALSE);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
path_filters PATH_FILTERS is a JSON_ARRAY of path filters used to search for specified patterns in file URLs.
esc_char

Specifies the escape character to be used in the path filters. If the escape character precedes the '%' or '_' in the pattern, the special character is interpreted literally, and not as a special pattern-matching character.

This parameter is optional and the default value for this parameter is NULL.

force

Forces the specified existing files to be overwritten in the cache even if the files were not modified.

This parameter is optional and the default value for the force parameter is FALSE.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
    owner        => 'SALES',
    table_name   =>'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata2.parquet"]'
);
END;
/

This procedure also supports tables exposed through mounted catalogs.

Usage Note

  • By default, the DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE procedure skips loading the files when:

    • The files were previously cached and still available in the cache.

    • The files have not been modified since they were last cached.

    However, you can use the force parameter to overwrite the files in the cache even if the files were not modified.

ADD_FILE Procedure

The DBMS_EXT_TABLE_CACHE.ADD_FILE procedure loads the specified file into the Lake Cache.

Syntax

DBMS_EXT_TABLE_CACHE.ADD_FILE (
      owner        IN VARCHAR2,
      table_name   IN VARCHAR2,
      file_url     IN VARCHAR2,
      force        IN BOOLEAN DEFAULT FALSE);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
file_url Specifies the file URL.
force

Forces the specified existing files to be overwritten in the cache even if the files were not modified.

This parameter is optional and the default value for force parameter is FALSE.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_FILE (
    owner        => 'SALES',
    table_name   => 'STORE_SALES',
    file_url     => 'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet'
);
END;
/

This procedure also supports tables exposed through mounted catalogs.

Usage Notes

  • The DBMS_EXT_TABLE_CACHE.ADD_FILE procedure skips loading the file into the cache if the specified file exists in the cache and has not been modified since the file was last cached.

  • You can use the force parameter to overwrite the file in the cache even if the file was not modified.

ADD_LATEST_FILES Procedure

The DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES populates one or more files into the Lake Cache. The files are populated based on the time interval determined by the SINCE argument and the current timestamp.

Syntax

DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES (
      owner        IN  VARCHAR2,
      table_name   IN  VARCHAR2,
      since        IN  INTERVAL DAY TO SECOND,
      max_files    IN  NUMBER,
      force        IN  BOOLEAN DEFAULT FALSE);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
since The since parameter accepts an INTERVAL DAY TO SECOND value and is used to calculate the time interval between the since value and the current timestamp. The resultant time interval is then used to populate the files, based on their last modified time.
max_files

Specify the max_files value to limit the number of files that can be populated into the Lake Cache.

This parameter is optional and defaults to unlimited when not specified.
force

Forces the specified existing files to be overwritten in the cache even if the files were not modified.

This parameter is optional and the default value for this parameter is FALSE.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES (
    owner        => 'SALES',
    table_name   => 'STORE_SALES',
    since        => INTERVAL '7' DAY,
    max_files    => 5,
    force        => TRUE);
END;
/

This procedure also supports tables exposed through mounted catalogs.

Usage Note

  • By default, the DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES procedure skips populating the files when:

    • The specified files were previously cached and still available in the cache.

    • The specified files have not been modified since they were last cached.

    However, you can use the force parameter to overwrite the files in the cache even if the files were not modified.

ADD_TABLE Procedure

The DBMS_EXT_TABLE_CACHE.ADD_TABLE procedure loads an entire table or a certain percentage of the source table into the cache. The source table can be either an external table or a table exposed through a mounted catalog.

Syntax

DBMS_EXT_TABLE_CACHE.ADD_TABLE (
      owner         IN VARCHAR2,
      table_name    IN VARCHAR2,
      percent_files IN NUMBER DEFAULT NULL,
      force         IN BOOLEAN DEFAULT FALSE);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
percent_files

Specifies the percentage of the table data to be cached. For example, 1 to 100.

By default, all files are loaded.
force

Forces the specified files to be overwritten in the cache even if the files were not modified.

This parameter is optional and the default value for force is FALSE.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_TABLE (
    owner         => 'SALES',
    table_name    => 'STORE_SALES',
    percent_files => 50);
END;
/

This procedure also supports tables exposed through mounted catalogs.

Usage Notes

  • The DBMS_EXT_TABLE_CACHE.ADD_TABLE attempts to cache the entire table or a specified percentage of the file content into the cache.

  • The percent_files and force parameters are mutually exclusive.

  • By default, the DBMS_EXT_TABLE_CACHE.ADD_TABLE procedure skips loading the files when:

    • The specified files were previously cached and still available in the cache.

    • The specified files have not been modified since they were last cached.

    However, you can use the force parameter to overwrite the files in the cache even if the files were not modified.

CLEAR Procedure

The DBMS_EXT_TABLE_CACHE.CLEAR procedure removes all files from a Lake Cache while retaining the cache.

Syntax

DBMS_EXT_TABLE_CACHE.CLEAR (
      owner         IN VARCHAR2,
      table_name    IN VARCHAR2);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.CLEAR (
    owner         => 'SALES',
    table_name    => 'STORE_SALES');
END;
/

This procedure also supports tables exposed through mounted catalogs.

CREATE_CACHE Procedure

The DBMS_EXT_TABLE_CACHE.CREATE_CACHE procedure creates a Lake Cache in an Autonomous AI Database instance.

Syntax

DBMS_EXT_TABLE_CACHE.CREATE_CACHE (
      owner            IN    VARCHAR2,
      table_name       IN    VARCHAR2,
      partition_type   IN    VARCHAR2,
      col_subset     IN VARCHAR2 DEFAULT NULL
);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
partition_type

Following are the valid values for the partition_type parameter:

  • FILE: partitions the Lake Cache by using the FILE$PATH and FILE$NAME invisible columns.

  • PATH: partitions the Lake Cache by using the FILE$PATH invisible column.

file$path: Specifies the file path text up to the beginning of the object name.

file$name: Specifies the object name, including all the text that follows the final '/'.

See External Table Metadata Columns for more information.

col_subset col_subset specifies the columns to cache. Provide a JSON_ARRAY of external table column names; only these columns are cached in the Lake Cache.

col_subset is optional. If you do not specify col_subset, the Lake Cache includes all columns by default.

Examples

Example: Create a Lake Cache including all columns

BEGIN
    DBMS_EXT_TABLE_CACHE.CREATE_CACHE (
      owner          => 'SALES',
      table_name     => 'STORE_SALES',
      partition_type => 'FILE');
END;
/

This procedure also supports tables exposed through mounted catalogs.

Example: Create a Lake Cache with a subset of columns

BEGIN
    DBMS_EXT_TABLE_CACHE.CREATE_CACHE (
    owner          => 'SALES',
    table_name     => 'STORE_SALES',
    partition_type => 'FILE',
    col_subset     => '["PROD_ID","STORE_ID","SALES_AMT"]');
END;
/

This procedure also supports tables exposed through mounted catalogs.

Usage Note

  • DBMS_EXT_TABLE_CACHE.CREATE_CACHE creates the Lake Cache in an Autonomous AI Database instance. Creating a cache is similar to creating a table in the database schema.

DISABLE Procedure

The DBMS_EXT_TABLE_CACHE.DISABLE procedure disables the specified Lake Cache. The cache is flagged as disabled; however, the data within the cache is retained.

Syntax

DBMS_EXT_TABLE_CACHE.DISABLE (
      owner        IN VARCHAR2,
      table_name   IN VARCHAR2
);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.

Example

BEGIN
    DBMS_EXT_TABLE_CACHE.DISABLE (
      owner          => 'SALES',
      table_name     => 'STORE_SALES');
END;
/

This procedure also supports tables exposed through mounted catalogs.

Usage Note

  • DBMS_EXT_TABLE_CACHE.DISABLE does not delete any data from the Lake Cache. Instead, it marks the cache as DISABLED, meaning the optimizer cannot use the cache for query rewrites.

DROP_BY_LIKE Procedure

The DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE procedure drops one or more files from the Lake Cache. The files are dropped based on the specified filters.

Syntax

DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
      owner        IN VARCHAR2,
      table_name   IN VARCHAR2,
      path_filters IN CLOB,
      esc_char     IN VARCHAR2 DEFAULT NULL);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
path_filters PATH_FILTERS is a JSON_ARRAY of path filters used to search for specified patterns in file URLs.
esc_char

Specifies the escape character to be used in the path filters. If the escape character precedes the '%' or '_' in the pattern, the special character is interpreted literally, and not as a special pattern-matching character.

This parameter is optional and the default value for this parameter is NULL.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
    owner        => 'SALES',
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata2.parquet"]'
);
END;
/

This procedure also supports tables exposed through mounted catalogs.

DROP_CACHE Procedure

The DBMS_EXT_TABLE_CACHE.DROP_CACHE procedure drops the specified Lake cache. This procedure drops the cache and releases the storage space associated with the cache.

Syntax

DBMS_EXT_TABLE_CACHE.DROP_CACHE (
      owner            IN    VARCHAR2,
      table_name       IN    VARCHAR2
);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.

Example

BEGIN
    DBMS_EXT_TABLE_CACHE.DROP_CACHE (
      owner          => 'SALES',
      table_name     => 'STORE_SALES');
END;
/

This procedure also supports tables exposed through mounted catalogs.

Usage Note

  • Dropping a cache removes its metadata from the data dictionary and deletes all its cached data.

DROP_FILE Procedure

The DBMS_EXT_TABLE_CACHE.DROP_FILE procedure drops the specified file from a Lake cache.

Syntax

DBMS_EXT_TABLE_CACHE.DROP_FILE (
      owner        IN VARCHAR2,
      table_name   IN VARCHAR2,
      file_url     IN VARCHAR2);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
file_url Specifies the file URL.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_FILE (
    owner        => 'SALES',
    table_name   => 'STORE_SALES',
    file_url     => 'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet'
);
END;
/

This procedure also supports tables exposed through mounted catalogs.

ENABLE Procedure

The DBMS_EXT_TABLE_CACHE.ENABLE procedure enables a previously disabled Lake cache. When a cache is created, it is enabled by default.

Syntax

DBMS_EXT_TABLE_CACHE.ENABLE (
    owner         IN    VARCHAR2,
    table_name    IN    VARCHAR2
);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.ENABLE (
    owner        => 'SALES',
    table_name   => 'STORE_SALES'
 );
END;
/

This procedure also supports tables exposed through mounted catalogs.

GET_AVAILABLE_QUOTA Function

Use the DBMS_EXT_TABLE_CACHE.GET_AVAILABLE_QUOTA function to retrieve the remaining available cache quota for a schema or for a specific external table cache.

Syntax

DBMS_EXT_TABLE_CACHE.GET_AVAILABLE_QUOTA (
    owner       IN   VARCHAR2,
    table_name  IN   VARCHAR2 DEFAULT NULL,
    no_cache    OUT  BOOLEAN,
    total_quota OUT  NUMBER
)
RETURN NUMBER;

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the external table name. If table_name is NULL, the function returns quota information for the schema.
no_cache Returns TRUE when no cache quota is available.
total_quota Returns the total quota available to the schema or cache.

Return Value

Returns the remaining available quota.

Examples

Example to check available quota for a schema:

SET SERVEROUTPUT ON;
DECLARE
  l_available_quota NUMBER;
  l_total_quota     NUMBER;
  l_no_cache        BOOLEAN;
BEGIN
  l_available_quota := DBMS_EXT_TABLE_CACHE.GET_AVAILABLE_QUOTA(
                         owner       => 'HR',
                         table_name  => NULL,
                         no_cache    => l_no_cache,
                         total_quota => l_total_quota);

  IF l_no_cache THEN
    DBMS_OUTPUT.PUT_LINE('NO_CACHE=TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('NO_CACHE=FALSE');
  END IF;

  DBMS_OUTPUT.PUT_LINE('TOTAL_QUOTA=' || l_total_quota);
  DBMS_OUTPUT.PUT_LINE('AVAILABLE_QUOTA=' || l_available_quota);
END;
/

Example to check available quota for a specific external table cache:

SET SERVEROUTPUT ON;
DECLARE
  l_available_quota NUMBER;
  l_total_quota     NUMBER;
  l_no_cache        BOOLEAN;
BEGIN
  l_available_quota := DBMS_EXT_TABLE_CACHE.GET_AVAILABLE_QUOTA(
                         owner       => 'SALES',
                         table_name  => 'STORE_SALES',
                         no_cache    => l_no_cache,
                         total_quota => l_total_quota);

  IF l_no_cache THEN
    DBMS_OUTPUT.PUT_LINE('NO_CACHE=TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('NO_CACHE=FALSE');
  END IF;

  DBMS_OUTPUT.PUT_LINE('TOTAL_QUOTA=' || l_total_quota);
  DBMS_OUTPUT.PUT_LINE('AVAILABLE_QUOTA=' || l_available_quota);
END;
/

GET_GLOBAL_CACHE_QUOTA Function

Use the DBMS_EXT_TABLE_CACHE.GET_GLOBAL_CACHE_QUOTA function to retrieve the resolved global cache quota.

Syntax

DBMS_EXT_TABLE_CACHE.GET_GLOBAL_CACHE_QUOTA
RETURN NUMBER;

Return Value

Returns the resolved global cache quota based on GLOBAL_CACHE_SIZE and GLOBAL_CACHE_PERCENT.

Usage Notes

  • 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.

Example

SET SERVEROUTPUT ON;
DECLARE
  l_global_quota NUMBER;
BEGIN
  l_global_quota := DBMS_EXT_TABLE_CACHE.GET_GLOBAL_CACHE_QUOTA;
  DBMS_OUTPUT.PUT_LINE('GLOBAL_CACHE_QUOTA=' || l_global_quota);
END;
/

GET_USER_PROPERTY Function

The function returns the caching preference for the specified schema and returns a Number.

MAX_CACHE_SIZE and MAX_CACHE_PERCENT are schema-level quota controls inside the global quota model.

Syntax

DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY (
    property_name   IN   VARCHAR2,
    owner           IN   VARCHAR2 DEFAULT NULL);

RETURN NUMBER;

Parameters

Parameter Description
property_name

Specifies the property name.

Following are the valid values for property_name:

  • MAX_CACHE_PERCENT

  • MAX_CACHE_SIZE
owner Specifies the schema name.

Return Values

Return Value Description
The MAX_CACHE_SIZE or MAX_CACHE_PERCENT value in Number. Depending on the property_name parameter, the function returns the MAX_CACHE_SIZE or MAX_CACHE_PERCENT value.

Usage Notes

  • When the global cache quota is known, MAX_CACHE_PERCENT is based on the resolved global quota. Otherwise, it is based on the schema or cache tablespace quota.

  • 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.

  • Manual caches and AUTO caches consume the same quota pool.

Example

SET SERVEROUTPUT ON;
DECLARE
  l_max_cache_sz NUMBER;
BEGIN
  l_max_cache_sz := DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY(
                      property_name => 'MAX_CACHE_SIZE',
                      owner         => 'SALES');
  DBMS_OUTPUT.PUT_LINE('MAX_CACHE_SIZE=' || l_max_cache_sz);
END;
/

REFRESH_TABLE Procedure

The DBMS_EXT_TABLE_CACHE.REFRESH_TABLE procedure deletes cached data for any files that no longer exist, and loads the files that are new or newly modified.

Syntax

DBMS_EXT_TABLE_CACHE.REFRESH_TABLE (
    owner        IN VARCHAR2,
    table_name   IN VARCHAR2);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.

Example

This example refreshes cached data for the STORE_SALES table in the SALES schema.

BEGIN
  DBMS_EXT_TABLE_CACHE.REFRESH_TABLE (
    owner        => 'SALES',
    table_name   => 'STORE_SALES');
END;
/

This procedure also supports tables exposed through mounted catalogs.

RETIRE_FILES Procedure

The DBMS_EXT_TABLE_CACHE.RETIRE_FILES drops files from the cache that are older than the specified interval. The files are deleted based on the time interval calculated using the BEFORE parameter value.

Syntax

DBMS_EXT_TABLE_CACHE.RETIRE_FILES (
    owner        IN VARCHAR2,
    table_name   IN VARCHAR2,
    before       IN INTERVAL DAY TO SECOND
);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
before The before parameter accepts an INTERVAL DAY TO SECOND value and is used to calculate the time interval between the before value and the current timestamp. The resultant time interval is then used to delete the files from the cache.

Example

BEGIN
 DBMS_EXT_TABLE_CACHE.RETIRE_FILES (
    owner        => 'SALES',
    table_name   => 'STORE_SALES',
    before       => INTERVAL '30' DAY);
END;
/

This procedure also supports tables exposed through mounted catalogs.

SET_USER_PROPERTY Procedure

The DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY procedure sets the caching preference for a schema.

Use MAX_CACHE_SIZE and MAX_CACHE_PERCENT to set schema-level quota preferences for policy-based caches.

Syntax

DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY (
    property_name    IN    VARCHAR2,
    property_value   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
property_value Specifies the property value.
owner Specifies the schema name.

Usage Notes

  • When the global cache quota is known, MAX_CACHE_PERCENT is based on the resolved global quota. Otherwise, it is based on the schema or cache tablespace quota.

  • 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 caches and AUTO caches consume the same quota pool.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY(
    property_name  => 'MAX_CACHE_PERCENT',
    property_value => 100,
    owner          => 'SALES');
END;
/

VALIDATE Procedure

The DBMS_EXT_TABLE_CACHE.VALIDATE procedure validates the Lake cache. An error is reported if the referenced source table is not found in the database.

Syntax

DBMS_EXT_TABLE_CACHE.VALIDATE (
      owner            IN    VARCHAR2,
      table_name       IN    VARCHAR2,
      raise_errors     IN    BOOLEAN DEFAULT TRUE);

Parameters

Parameter Description
owner Specifies the schema name.
table_name Specifies the source table name. The source table can be either an external table or a table exposed through a mounted catalog.
raise_errors Reports when a Lake cache is marked invalid.

Example

BEGIN
  DBMS_EXT_TABLE_CACHE.VALIDATE (
    owner         => 'SALES',
    table_name    => 'STORE_SALES',
    raise_errors  => TRUE);
END;
/

This procedure also supports tables exposed through mounted catalogs.