Oracle Cloud Infrastructure Documentation

Refresh External Partitioned Tables with Updated or Deleted Source Files

You can use DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE to refresh an external partitioned table. Use this procedure when new partitions are added or when partitions are removed from the object store source.

Note

Only use DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE when the partitioned external table is created with DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE and the file_url_path parameter.

To refresh a partitioned external table:

  1. Refresh an external partitioned table on top of your Cloud Object Store source files using the procedure  DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE.

    For example:

    BEGIN
   DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE(table_name => 'MYSALES');
END;
/

    DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE uses the credential information from the corresponding DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE with the specified table_name to access Cloud Object Store.

    See SYNC_EXTERNAL_PART_TABLE Procedure for detailed information about the parameter.

  2. The partition information is now refreshed and you can run queries on the updated external partitioned table. with new partitions available or with partitions that were removed no longer available in the external partitioned table.