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 into Autonomous Database.

Parent topic: Autonomous Database Supplied Package Reference

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 an external table cache.

ADD_FILE Procedure

Adds a file into the external table cache.

ADD_TABLE Procedure

Adds files from the specified external table into an external table cache.

CLEAR Procedure

Clears an external table cache.

CREATE_CACHE Procedure

Creates an external table cache.

DISABLE Procedure

Disables an external table cache.

DROP_BY_LIKE Procedure

Drop files from an external table cache based on the specified filters.

DROP_CACHE Procedure

Drops an external table cache.

DROP_FILE Procedure

Removes the specified external table file from the external table cache.

ENABLE Procedure

Enables a previously disabled external table cache.

GET_USER_PROPERTY Function

Retrieves the caching preference for an external table cache.

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 an external table cache.

VALIDATE Procedure

Validates an external table cache.
  • ADD_BY_LIKE Procedure
    The DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE procedure loads one or more specified files from Object Store into the external table cache.
  • ADD_FILE Procedure
    The DBMS_EXT_TABLE_CACHE.ADD_FILE procedure loads the specified file into the external table cache.
  • ADD_LATEST_FILES Procedure
    The DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES loads one or more files into the external table cache. The files are loaded based on the time interval determined by the SINCE argument and the current timestamp.
  • ADD_TABLE Procedure
    The DBMS_EXT_TABLE_CACHE.ADD_TABLE procedure loads an entire table or a certain percentage of the external table into the cache.
  • CLEAR Procedure
    The DBMS_EXT_TABLE_CACHE.CLEAR procedure removes all files from an external table cache while retaining the cache.
  • CREATE_CACHE Procedure
    The DBMS_EXT_TABLE_CACHE.CREATE_CACHE procedure creates an external table cache in an Autonomous Database instance.
  • DISABLE Procedure
    The DBMS_EXT_TABLE_CACHE.DISABLE procedure disables the specified external table cache. The cache is flagged as disabled; however, the data within the cache is retained.
  • DROP_BY_LIKE Procedure
    The DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE procedure drops one or more files from the external table cache. The files are dropped based on the specified filters.
  • DROP_CACHE Procedure
    The DBMS_EXT_TABLE_CACHE.DROP_CACHE procedure drops the specified external table cache. This procedure drops the cache and releases the storage space associated with the cache.
  • DROP_FILE Procedure
    The DBMS_EXT_TABLE_CACHE.DROP_FILE procedure drops the specified file from an external table cache.
  • ENABLE Procedure
    The DBMS_EXT_TABLE_CACHE.ENABLE procedure enables a previously disabled external table cache. When a cache is created, it is enabled by default.
  • GET_USER_PROPERTY Function
    The function returns the caching preference for the specified schema and returns a Number.
  • 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.
  • SET_USER_PROPERTY Procedure
    The DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY procedure sets the caching preference for a schema.
  • VALIDATE Procedure
    The DBMS_EXT_TABLE_CACHE.VALIDATE procedure validates the external table cache. An error is reported if the referenced external table is not found in the database.

Parent topic: DBMS_EXT_TABLE_CACHE Package

ADD_BY_LIKE Procedure

The DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE procedure loads one or more specified files from Object Store into the external table 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 external table name.
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;
/

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 external table 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 external table name.
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;
/

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 loads one or more files into the external table cache. The files are loaded 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 external table name.

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 load the files, based on their last modified time.

max_files

Specify the max_files value to limit the number of files that can be loaded into the external table 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;
/

Usage Note

  • By default, the DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES 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.

ADD_TABLE Procedure

The DBMS_EXT_TABLE_CACHE.ADD_TABLE procedure loads an entire table or a certain percentage of the external table into the cache.

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 external table name.

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;
/

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 an external table 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 external table name.

Example

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

CREATE_CACHE Procedure

The DBMS_EXT_TABLE_CACHE.CREATE_CACHE procedure creates an external table cache in an Autonomous Database instance.

Syntax

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

Parameters

Parameter Description

owner

Specifies the schema name.

table_name

Specifies the name of the external table.

partition_type
Following are the valid values for the partition_type parameter:

  • FILE: partitions the external table cache by using the FILE$PATH and FILE$NAME invisible columns.

  • PATH: partitions the external table 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.

Example

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

Usage Note

  • DBMS_EXT_TABLE_CACHE.CREATE_CACHE creates the external table cache in an Autonomous 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 external table 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 external table name.

Example

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

Usage Note

  • DBMS_EXT_TABLE_CACHE.DISABLE does not delete any data from the external table 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 external table 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 external table name.
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;
/

DROP_CACHE Procedure

The DBMS_EXT_TABLE_CACHE.DROP_CACHE procedure drops the specified external table 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 external table name.

Example

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

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 an external table 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 external table name.

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;
/

ENABLE Procedure

The DBMS_EXT_TABLE_CACHE.ENABLE procedure enables a previously disabled external table 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 external table name.

Example

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

GET_USER_PROPERTY Function

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

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_SIZE value in Number.

Depending on the property_name parameter the number returned is the MAX_CACHE_SIZE or MAX_CACHE_SIZE value.

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;                                                                 
/

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 external table name.

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;
/

SET_USER_PROPERTY Procedure

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

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.

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 external table cache. An error is reported if the referenced external 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 external table name.

raise_errors

Reports when an external table cache is marked invalid.

Example

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