Query External Data Pump Dump Files

You can also query Oracle Data Pump dump files in the Cloud by creating an external table using DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

The source files to create this type of external table must be exported from the source system using the ORACLE_DATAPUMP access driver in External Tables. See Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver for details on exporting using the ORACLE_DATAPUMP access driver.

To create an external table you first move the Oracle Data Pump dump files that were exported using the ORACLE_DATAPUMP access driver to your Object Store and then use DBMS_CLOUD.CREATE_EXTERNAL_TABLE to create the external table.

The source files in this example are the Oracle Data Pump dump files, exp01.dmp and exp02.dmp.

1. Store your object store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL.

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => '[e-mail]',
       password => 'password' );
   END;
   /
   

   This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials you can then use the same credential name for creating external tables.

   See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.

2. Create an external table on top of your source files using the procedure  DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

   For example:

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'CHANNELS_EXT',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp01.dmp,
                        https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp02.dmp'
       format => json_object('type' value 'datapump', 'rejectlimit' value '1'),
       column_list => 'CHANNEL_ID NUMBER, CHANNEL_DESC VARCHAR2(20), CHANNEL_CLASS VARCHAR2(20)' );
   END;
   /
   

   The parameters are:

   • table_name: is the external table name.

   • credential_name: is the name of the credential created in the previous step.

   • file_uri_list: is a comma delimited list of the Data Pump dump files you want to query.

   • format: defines the options you can specify to describe the format of the source file defines the options you can specify to describe the format of the source file. When you specify the type 'datapump', the only other valid format parameter is 'rejectlimit'.

   • column_list: is a comma delimited list of the column definitions in the source files.

   In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

   You can now run queries on the external table you created in the previous step. For example:

   SELECT count(*) FROM channels_ext;

   By default the database expects all rows in the external data file to be valid and match both the target data type definitions as well as the format definition of the files. As part of validation, DBMS_CLOUD makes sure all the necessary dump file parts are there and also checks that the dump files are valid and not corrupt (for example exp01.dmp, exp02.dmp, and so on). You can use the DBMS_CLOUD format option rejectlimit to suppress these errors. As an alternative, you can also validate the external table you created to see the error messages and the rejected rows. See Validate External Data for more information.

   For detailed information about the parameters, see CREATE_EXTERNAL_TABLE Procedure.

   See DBMS_CLOUD URI Formats for more information on the supported cloud object storage services.