Oracle Cloud Infrastructure Documentation

Use External Table Cache to Improve Performance for External Tables

External Table Cache in Oracle Autonomous AI Database enables you to cache frequently accessed data from external tables in your database.

Note

External Table Cache is only supported for Oracle AI Database 26ai.

Parent topic: Features

About External Table Cache in Autonomous AI Database

An external table cache is a storage area in your Autonomous AI Database that stores the data from an external table.

External data is not managed by the database; however, you can use the external tables to query data outside of the database. Queries on external tables will not be as fast as queries on database tables because each time you access the data it needs to be fetched from the external files stored on Object Store.

The external table cache enables you to store frequently accessed external data locally within your Autonomous AI Database, enabling subsequent queries to retrieve data directly from the cache. When the external data is stored in your database, queries on external tables run significantly faster than when retrieving data from external tables. This caching mechanism is fully transparent to applications, requiring no changes to SQL statements or existing workflows.

You can create external table cache for partitioned and non-partitioned external tables created on Parquet, ORC, AVRO, CSV and Iceberg Tables. You can populate new or updated files into the cache easily, and the database automatically invalidates data from deleted or inaccessible files, ensuring that queries always return the most current data. If external files are removed or table credentials are revoked, the corresponding cached data is immediately invalidated and becomes inaccessible, maintaining data integrity and security.

External table caches in your database can be managed automatically or through policy-based settings. With policy-based cache management, you can define simple policies to populate, refresh, and retire files from the cache, giving you precise control over cache contents and maintenance.

The following are some of the benefits of using the external table cache:

  • Improved Performance for analytics: Queries are several times faster for your frequently accessed external data - ideal for dashboards, reports, and analytical tools that access the same data regularly.

  • 100% Transparent: The caching mechanism is entirely transparent; applications can benefit from improved speed without requiring any changes to their queries, dashboards, or applications.

  • Lower Cloud Costs: In a multi-cloud application, caching reduces the need for repeated external data retrievals from remote storage, thereby reducing data egress fees associated with accessing data across regions or clouds.

  • Fine-grained, flexible caching control: You can cache all files, a percentage of files, or only the most recently updated data. You can control the cached data, cache size, and storage limits for external table caches.

See Query External Data for more information.

Prerequisites to Create External Table Cache

Lists the prerequisites to create the external table cache.

  • You must be logged in as the ADMIN user or have the EXECUTE privilege on the DBMS_EXT_TABLE_CACHE and DBMS_CACHE packages.

  • You can only create an external table cache in your own schema and for the external tables that you own.

  • You must have an appropriate space quota allocated for your schema to ensure there is sufficient storage capacity for the cache data.

  • You must have credentials to access external table files stored on Object Store. You don't need to create credentials if you enable resource principal credentials for accessing Oracle Cloud Infrastructure Object Store.

Create and Manage External Table Cache in Autonomous AI Database

Using the external table cache, you can cache the data from external tables within your database.

The external table cache is created as a schema object in your database, which is allocated physical space similar to how tables and indexes are stored in data files. When you create an external table cache, a new table is created in your schema, and any space quota limits that are set for your schema also apply to the external table cache.

External table caches management can be Automatic or Policy-based. With automatic cache management, the database creates, populates, refreshes, and drops caches based on external table query patterns. With policy-based management, you define how caches are created, populated, refreshed, and retired, giving you precise control over cache contents and lifecycle.

Topics

Parent topic: Use External Table Cache to Improve Performance for External Tables

Use Policy-Based Caching for External Tables

Describes how to use policy-based caching for external tables in Autonomous AI Database.

Policy-based caching provides you with explicit control over how external data is cached, refreshed, and managed within the database. In this approach, you define caching policies and manage the entire cache lifecycle using PL/SQL procedures available in the DBMS_EXT_TABLE_CACHE package. These procedures allow you to explicitly perform various cache lifecycle operations, such as creating and populating caches, dropping files from the cache, and enabling or disabling the caches.

This approach gives you fine-grained control over cache behavior. You can specify which external table files or what percentage of external table’s data should be cached, thereby ensuring optimal use of cache space based on workload requirements. Procedures such as ADD_BY_LIKE and ADD_LATEST_FILES let you filter and populate files into the cache based on several parameters, such as file name patterns, modification times, or data freshness criteria. Similarly, you can use procedures such as CLEAR, RETIRE_FILES, or DROP_BY_LIKE to remove files from the cache.

Since policy-based caches are not managed by an automatic eviction algorithm, the database does not automatically drop them under space pressure. If cache space becomes unavailable, new files may fail to populate until additional space is freed. This approach provides greater flexibility, and is ideal for workloads where you need more control over cache contents.

The following flowchart outlines the steps for managing policy-based caches using the DBMS_EXT_TABLE_CACHE package. It includes key steps such as cache creation, population, and drop.


Description of adb_external_table_cache.png follows
Description of the illustration adb_external_table_cache.png

Topics

Parent topic: Create and Manage External Table Cache in Autonomous AI Database

Create External Table Cache

Run DBMS_EXT_TABLE_CACHE.CREATE_CACHE to create an external table cache.

When the cache is created, it is initially empty and enabled for population. The cache size increases each time a file is added, depending on the defined space quota limits for the schema, until it reaches the assigned limits. See Set Optional Sizing Preferences for Policy-Based Caches for more information.

  1. Use DBMS_EXT_TABLE_CACHE.CREATE_CACHE to create external table cache for your schema. For example:
    BEGIN
    DBMS_EXT_TABLE_CACHE.CREATE_CACHE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES',
      partition_type => 'PATH');                                                                 
END;                                                                 
/

    This creates a cache for the STORE_SALES table in the SALES schema. The STORE_SALES is an external table pointing to data stored on Object Store.

    The owner parameter specifies the schema name. This example creates an external table cache for the SALES user.

    The partition_type parameter specifies the partitioning method to use for the external table cache. This example partitions the cache using the FILE$PATH column. The FILE$PATH is an invisible column that specifies the file path text up to the beginning of the object name.

    See the following for more information:

    CREATE_CACHE Procedure

    External Table Metadata Columns

  2. When you first create an external table cache, its metadata is stored in the data dictionary, however no space is allocated for the cache data. You can query the USER_EXTERNAL_TAB_CACHES view to verify the cache creation.
    SELECT external_table_name, cached, disabled 
  FROM user_external_tab_caches;

    See DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views for more information.

    Use the DBMS_EXT_TABLE_CACHE.VALIDATE procedure to validate an external table cache. An error is reported if the referenced external table is not found in the database.

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

    See VALIDATE Procedure for more information.

Populate Files into External Table Cache

Shows examples to populate files into the external table cache.

After you create a cache, you can populate files into the cache. Populating files loads the contents of the specified external table files into the cache. You can choose to populate all files from a table, a specific percentage of the table, or specify a filter condition to limit the files you want to populate. For example, you can filter the files based on their names or a date range.

Note

  • Depending on the space quota allocated for the schema, Oracle attempts to populate files into the cache. If the assigned quota limit is reached, Oracle stops populating files unless the required space is allocated.

  • The external table cache does not refresh automatically. To update the cache when a file on the Object Store is modified, you must repopulate the file.

  • When a file is deleted from the Object Store, the corresponding cached data immediately becomes invalid and cannot be retrieved.

Add Table to External Table Cache

Use DBMS_EXT_TABLE_CACHE.ADD_TABLE to populate an entire table or a certain percentage of the external table into the cache.

Examples

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

This example attempts to populate the STORE_SALES table into the cache, skipping any existing files that have already been populated. 

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

This example attempts to populate 80% of the STORE_SALES table into the cache, skipping any existing files that have already been populated.

The percent_files parameter is optional; if you do not specify this parameter, the entire table is populated into the cache.

See ADD_TABLE Procedure for more information.

Add Files to External Table Cache

You can use the following procedures to add one or more files to the external table cache:

  • ADD_FILE: to add a single file into the cache.

  • ADD_BY_LIKE: to add one or more specified files based on the specified path filters.

  • ADD_LATEST_FILES: to add one or more files based on the specified time interval.

Examples

Use the DBMS_EXT_TABLE_CACHE.ADD_FILE procedure to populate a single file into the external table cache. For 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 example populates data from the salesdata.parquet file into the cache.

This example skips populating the file into the cache if the specified file exists in the cache and has not been modified since the file was last cached.

See ADD_FILE Procedure for more information.

Use the DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE procedure to populate one or more files into the external table cache. For 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/sales%.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/customer%.parquet"]'
);
END;
/

This example populates all files with names beginning with sales or customer, while excluding files that have already been populated. 

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/sales#_data1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data2.parquet"]',
    esc_char     => '#',
    force        => TRUE);
END;
/

This example populates the sales_data1.parquet and sales_data2.parquet files into the cache.

In this example, '#' character is defined as the escape character. The '_' character following '#' is treated as a literal underscore, not as a wildcard matching any single character.

See ADD_BY_LIKE Procedure for more information.

Use the DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES procedure to populate one or more files based on the last modified date into the external table cache. For 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;
/

The since parameter specifies the time interval; only files modified within the last seven (7) days are eligible to be populated into the cache.

The max_files parameter limits the number of files that can be populated into the cache. This example populates only five (5) files.

The force parameter forces the specified files to be overwritten in the cache even if the files were not modified.

See ADD_LATEST_FILES Procedure for more information.

Query the following data dictionary views to list the files cached in the external table cache:
Drop Files from External Table Cache

Shows examples to drop files from external table cache.

You can remove all files from the cache, or specify filter conditions to drop one or more files from the cache. For example, you can filter the files by their names or based on a specific time interval.

Clear External Table Cache

Use DBMS_EXT_TABLE_CACHE.CLEAR to drop all files from the external table cache. For example: 

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

This example drops all files from the STORE_SALES cache and deallocates all space used by the removed files.

See CLEAR Procedure for more information.

Drop Files from External Table Cache

You can use the following procedures to drop one or more files from the external table cache:

  • DROP_FILE: to drop a single file from the cache.

  • DROP_BY_LIKE: to drop one or more files from the cache based on the specified path filters.

  • RETIRE_FILES: to drop one or more files from the cache based on the specified interval.

Examples

Use DBMS_EXT_TABLE_CACHE.DROP_FILE to drop a file from the external table cache. For 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 example drops the salesdata.parquet file from the cache and deallocates all space used by the removed file.

See DROP_FILE Procedure for more information.

Use DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE to drop one or more files based on the path_filters parameter. For 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/salesdata.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata1.parquet"]'
);
END;
/

This example drops the salesdata.parquet and salesdata1.parquet files from the cache and deallocates all space used by the removed files. 

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/sales#_data1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data1.parquet"]'
);
END;
/

This example drops the sales#_data1 and sales#_data2 files from the cache and deallocates all space used by the removed files.

In this example, the '#' character is defined as the escape character. The '_' character following '#' is treated as a literal underscore, not as a wildcard matching any single character.

See DROP_BY_LIKE Procedure for more information.

Use DBMS_EXT_TABLE_CACHE.RETIRE_FILES to drop one or more files based on the specified interval. For example: 

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

This example drops files that are older than thirty (30) days from the cache and deallocates all space used by the removed files.

See RETIRE_FILES Procedure for more information.

The above examples remove one or more files from the cache while retaining the cache. You can load files again into the cache when necessary. See Populate Files into External Table Cache for more information.

Disable and Enable External Table Cache

Shows examples to disable and enable the external table cache.

Run DBMS_EXT_TABLE_CACHE.DISABLE to disable external table cache from the database. Disabling a cache does not delete data from the cache; instead, the cache is flagged as DISABLED, and the optimizer cannot use the cache for query rewrites.

Example

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

This example disables the STORE_SALES cache.

See DISABLE Procedure for more information.

After you disable an external table cache, use DBMS_EXT_TABLE_CACHE.ENABLE to enable the cache.

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

This example enables the STORE_SALES cache.

See ENABLE Procedure for more information.

Drop External Table Cache

Shows an example to drop the external table cache.

Run DBMS_EXT_TABLE_CACHE.DROP_CACHE to drop an external table cache. The DBMS_EXT_TABLE_CACHE.DROP_CACHE procedure removes the specified external table cache from the database and releases the storage space associated with the cache.

Example:

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

This example drops the STORE_SALES cache from the SALES schema.

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

See DROP_CACHE Procedure for more information.

Query the USER_EXTERNAL_TAB_CACHES view to verify that the cache has been dropped. For example:
SELECT external_table_name, cached
  FROM user_external_tab_caches;

See DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views for more information.

Set Optional Sizing Preferences for Policy-Based Caches

Describes how to set sizing preferences for policy-based external table caches in Autonomous AI Database.

By default, the external table cache is disabled for a user. To enable and create the external table cache use the DBMS_EXT_TABLE_CACHE.CREATE_CACHE procedure. The cache is created in your default schema and inherits any space quota limits defined for your schema. However, you can also use the DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY procedure to define space quotas for external table cache. You use the PROPERTY_NAME and PROPERTY_VALUE parameters of the DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY procedure to set the space quota limits.

The PROPERTY_NAME parameter accepts MAX_CACHE_SIZE and MAX_CACHE_PERCENT values. The MAX_CACHE_SIZE property specifies the total external cache size in bytes. The MAX_CACHE_PERCENT property specifies the total external cache size as a percentage of the specified user's quota.

Examples

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

This example sets the caching preference to MAX_CACHE_PERCENT for the SALES schema.

The property_value is 50%, which specifies that the cache space quota for the SALES schema is a maximum of 50% of the total space quota defined for SALES. 

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

This example sets the caching preference to MAX_CACHE_SIZE for the SALES schema.

The property_value is 5368709120, which specifies that the maximum cache size for the SALES schema is up to 5GB.

See SET_USER_PROPERTY Procedure and CREATE_CACHE Procedure for more information.

Use DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY to retrieve the cache size properties.

Example:

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

See GET_USER_PROPERTY Function for more information.

Note the following precedence order for setting the cache size properties:

  • When MAX_CACHE_SIZE, MAX_CACHE_PERCENT, and the space quota are defined, MAX_CACHE_PERCENT takes precedence over MAX_CACHE_SIZE.

  • When only MAX_CACHE_SIZE is defined, and MAX_CACHE_PERCENT or space quota is not defined, MAX_CACHE_SIZE takes precedence.

  • When only the space quota is defined and MAX_CACHE_SIZE and MAX_CACHE_PERCENT are not defined, the cache size quota defaults to 10% of the total schema quota.

  • When MAX_CACHE_SIZE, MAX_CACHE_PERCENT, or the space quota is not defined, the cache space quota defaults to UNLIMITED.

Note

To monitor cache space usage, query the CACHE_CUR_SIZE column in the ALL_EXTERNAL_TAB_CACHES views. See DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views for more information.

Use Automatic Caching for External Table

Describes how to use automatic caching for external tables in Autonomous AI Database.

When you enable automatic caching for your database, the database automatically manages the entire cache lifecycle, including creation, population, refresh, and drop, without requiring your intervention. Oracle uses SQL Tuning Sets (STS) to determine which external tables can benefit from caching, when to refresh the caches, and when to drop them based on usage patterns and available storage space. This approach reduces your overhead for cache management, as cache usage is continuously monitored, ensuring that frequently accessed external table data remains available in the cache to improve query response times.

By default, automatic caching is not enabled in your database. To enable it, you must set the cache size to a non-zero value using procedures such as DBMS_CACHE.SET_USER_PROPERTY provided in the DBMS_CACHE package. You can configure automatic caching either for a specific user or as the default setting for all database users, depending on your requirements.

When automatic caching of external tables is enabled, Oracle creates external table caches that are marked AUTO, and populates all corresponding data from a external table into its cache if quota permits. The AUTO caches are refreshed automatically on a regular schedule. However, you can also use DBMS_CACHE.REFRESH or DBMS_CACHE.CLEAR procedures to refresh or drop your caches, respectively.

Oracle manages the AUTO caches 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 cycle to release the storage space.

See the following for more information:

Topics

Parent topic: Create and Manage External Table Cache in Autonomous AI Database

Enable Automatic Caching for External Tables

Shows examples of how to enable automatic caching for external tables in your database.

By default, automatic caching is disabled, you can enable automatic caching for your external tables either globally for all database users or for a specific user. After automatic caching is enabled, the database automatically creates external table caches based on the external table query usage statistics available in the SQL Tuning Sets (STS). All newly created caches are marked as AUTO. Existing caches continue to follow the policy-based cache management settings.

When enabling automatic caching, you can optionally specify the scope of the cache refresh, meaning which caches are eligible for refresh during each refresh cycle, and define the time window allowed to complete the refresh process.

You use DBMS_CACHE.SET_GLOBAL_PROPERTY or DBMS_CACHE.SET_USER_PROPERTY to set AUTO caching properties, including properties to enable automatic caching globally or for a specified user, respectively. User-level caching preferences take precedence over global caching preferences. Use the PROPERTY_NAME and PROPERTY_VALUE parameters of these procedures to set space quota limits for external table caches.

Depending on the specified space quota, Oracle creates the caches and attempts to populate the entire external table data into the cache. The population process fails if the cache size is not sufficient to accommodate the entire data of the external table.

  1. Use DBMS_CACHE.SET_GLOBAL_PROPERTY to enable automatic caching for all database users.

    Examples:

    Example to enable automatic caching for all database users:

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

    This example sets the caching preference to MAX_CACHE_PERCENT as the default for all database users and sets the external table cache quota to maximum of 20% of the total assigned user quota. You can override this default setting for individual users by using the DBMS_CACHE.SET_USER_PROPERTY procedure.

    Example to set the automatic refresh mode for all database user:
    BEGIN
 DBMS_CACHE.SET_GLOBAL_PROPERTY (
    property_name       => 'AUTO_REFRESH_MODE', 
    property_value_str  => 'NEW');                                                              
END;                                                                 
/

    This specifies the scope at which AUTO caches are refreshed during each refresh cycle. The property_value_str value can be overridden at the schema level using the DBMS_CACHE.SET_USER_PROPERTY procedure.

    Example to set the maximum time window allowed to complete the refresh:
    BEGIN
 DBMS_CACHE.SET_GLOBAL_PROPERTY (
    property_name       => 'MAX_REFRESH_WINDOW', 
    property_value_num  => 20);                                                              
END;                                                                 
/

    This example sets MAX_REFRESH_WINDOW to twenty (20) seconds.

    See SET_GLOBAL_PROPERTY Procedure for more information.

    Note

    The MAX_REFRESH_WINDOW property can be defined only at the database level, this property cannot be set at the schema level.

    Run the DBMS_CACHE.GET_GLOBAL_PROPERTY to retrieve the default automatic caching preferences for external tables. For 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;
/

    See GET_GLOBAL_PROPERTY Procedure for more information.

  2. You use DBMS_CACHE.SET_USER_PROPERTY to enable automatic caching of external table for a specific user. For example:
    BEGIN
 DBMS_CACHE.SET_USER_PROPERTY (
        property_name       => 'MAX_CACHE_PERCENT', 
        property_value_num  => 50,
        owner               => 'SALES');                                                                
END;                                                                 
/

    This example overrides the global caching preferences with the specified values in DBMS_CACHE.SET_USER_PROPERTY.

    See SET_USER_PROPERTY Procedure for more information.

    Use DBMS_CACHE.GET_USER_PROPERTY to retrieve automatic caching preferences for external tables for a specified user. For example: 

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

    See GET_USER_PROPERTY Procedure for more information.

Note

To monitor cache space usage, query the CACHE_CUR_SIZE column in the ALL_EXTERNAL_TAB_CACHES views. See DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views for more information.
Refreshing External Table Caches

Shows an example to refresh AUTO caches for a specified schema.

The AUTO caches are refreshed automatically on a regular schedule. Depending on the specified refresh type, database may:

  • Add new caches.

  • Drop any invalid caches (the caches that becomes inaccessible are marked as invalid and are dropped in the subsequent refresh cycle.)

  • Update or repopulate existing caches.

  • Drop the least recently accessed caches when under space pressure.

Alternatively, you can also use the DBMS_CACHE.REFRESH procedure to perform an on-demand refresh for all caches of a specified user.

  1. You use DBMS_CACHE.REFRESH to refresh all external table caches of a specified user. For example:
    BEGIN
    DBMS_CACHE.REFRESH (      
      owner          => 'SALES',
      refresh_type   => 'ALL');                                                                 
END;                                                                 
/

    This example updates existing caches and creates new external table caches for the SALES schema, as needed.

    The refresh_type property specifies the scope at which the refresh is performed. The property can have one of the following values:

    • ALL: All existing AUTO caches for the SALES schema are refreshed and if needed new caches are created.

    • CURRENT: only existing caches are refreshed, no new caches are added.

    • NEW: only new caches are created.

    See REFRESH Procedure for more information.

Drop External Table Caches

Shows an example to drop AUTO caches for a specified schema.

During each refresh cycle, invalid caches and the least recently accessed caches are dropped from the database. Alternatively, you can use the DBMS_CACHE.CLEAR procedure to drop all caches for a specified user.

  1. You use DBMS_CACHE.CLEAR to drop all external table caches for a specified user. For example:
    BEGIN
 DBMS_CACHE.CLEAR (
    owner => 'SALES');                                                                
END;                                                                 
/

    This example drops all external table caches for the SALES schema.

    See CLEAR Procedure for more information.

View External Table Cache Information

Autonomous AI Database provides views that allow you to monitor the external table cache.

View Description

DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views

Provides information either about all external table caches in the database or about a user's external table caches.

ALL_EXTERNAL_TAB_CACHE_LOCATIONS

Provides information about the files in Cloud Storage that are accessible to the current user and belong to cached external tables.

USER_EXTERNAL_TAB_CACHE_LOCATIONS

Provides information about the files in cloud storage that are owned by the current user and belong to cached external tables. This view does not display the OWNER column.

External Table Cache Notes

Lists important notes and restrictions about using external table cache in an Autonomous AI Database.

  • You can only create an external table cache in your own schema and for the external tables that you own.

  • An external table cache can contain files only from a single external table.

  • You cannot grant privileges on an external table cache, and you can only access data from the external table cache that you own.

  • When a file is deleted from Object Store, the corresponding data in the cache is flagged as invalid and cannot be accessed.