Oracle recommends using the latest Oracle Data Pump version for importing data from Data Pump files into your Autonomous Database, as it contains enhancements and fixes for a better experience.

1. Store your Cloud Object Storage credential using DBMS_CLOUD.CREATE_CREDENTIAL.

   For example, to create Oracle Cloud Infrastructure Auth Token credentials with the default Identity domain:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => 'adb_user@example.com',
       password => 'password'
     );
   END;
   /

   For OCI Object Storage the username parameter value must include the Identity domain and the user name from your profile. You can find the Identity domain associated with a user in the Oracle Cloud Infrastructure Console. For example, with the oracleidentitycloudservice Identity domain, this would be:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => 'oracleidentitycloudservice/adb_user@example.com',
       password => 'password'
     );
   END;
   /

   For more information on Oracle Cloud Infrastructure Auth Token authentication see CREATE_CREDENTIAL Procedure.

   For example, to create Oracle Cloud Infrastructure Signing Key based credentials:

   BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL (
          credential_name => 'DEF_CRED_NAME',
          user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
          tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
          private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
          fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
   END;
   /

   For more information on Oracle Cloud Infrastructure Signing Key based credentials see CREATE_CREDENTIAL Procedure.

   Supported credential types:

   • Data Pump Import supports Oracle Cloud Infrastructure Auth Token based credentials and Oracle Cloud Infrastructure Signing Key based credentials.

     For more information on the credential types for Oracle Cloud Infrastructure Cloud Object Storage, see CREATE_CREDENTIAL Procedure.

   • Data Pump supports using an Oracle Cloud Infrastructure Object Storage pre-authenticated URL for the dumpfile parameter. When you use a pre-authenticated URL, providing the credential parameter is required and the credential value can be NULL. See Using Pre-Authenticated Requests for more information.

   • Data Pump supports using a resource principal credential with impdp. See Import Data Using Oracle Data Pump Version 18.3 or Later with OCI Resource Principal for more information.

2. Run Data Pump Import with the dumpfile parameter set to the list of file URLs on your Cloud Object Storage and the credential parameter set to the name of the credential you created in the previous step. For example:

   impdp admin/password@db2022adb_high \       
        directory=data_pump_dir \       
        credential=def_cred_name \       
        dumpfile= https://namespace-string.objectstorage.us-ashburn-1.oci.customer-oci.com/n/namespace-string/b/bucketname/o/export%l.dmp \
        parallel=16 \
        encryption_pwd_prompt=yes \
        exclude=cluster,indextype,db_link

   Notes for Data Pump parameters:

   • If during the export with expdp you used the encryption_pwd_prompt=yes parameter then use encryption_pwd_prompt=yes and input the same password at the impdp prompt that you specified during the export.

   • The dumpfile parameter supports the %L and %l wildcards in addition to the legacy %U and %u wildcards. For example, dumpfile=export%L.dmp. Use the %L or %l wildcard for exports from Oracle Database Release 12.2 and higher. This wildcard expands the dumpfile file name into a 3-digit to 10-digit, variable-width incrementing integer, starting at 100 and ending at 2147483646.

     Use the legacy %U or %u wildcard for exports from Oracle Database prior to Release 12.2. If you use this option and more than 99 dump files are needed, you must specify multiple dumpfile names, each with the %U or %u parameter.

   • If your bucket and data do not reside in a commercial (OC1) realm, where OCI Dedicated Endpoints are not supported, use the following URI format for dumpfile:

     https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname/o/filename

   • The credential parameter cannot be an Azure service principal, Amazon Resource Name (ARN), or a Google service account. See Configure Policies and Roles to Access Resources for more information on resource principal based authentication.

   • When you specify the dumpfile value as a pre-authenticated URL, the credential parameter is required and the value can be NULL.

     See Use Oracle Data Pump Import with a Pre-Authenticated URL for details on the steps required to use a pre-authenticated URL.

   For dumpfile, this example uses the recommended URI format using OCI Dedicated Endpoints for commercial realm (OC1). The namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Object Storage Dedicated Endpoints, Regions and Availability Domains, and Understanding Object Storage Namespaces for more information.

   For the dump file URL format for different Cloud Object Storage services, see DBMS_CLOUD URI Formats.

   In this example the following are excluded during the Data Pump Import:

   • Clusters

   • Indextypes

   • Database links

   To perform a full import or to import objects that are owned by other users, you need the DATAPUMP_CLOUD_IMP role.

   For information on disallowed objects in Autonomous Database, see SQL Commands.

   See Notes for Importing with Oracle Data Pump for additional notes for using Oracle Data Pump Import.

   See Oracle Data Pump Import and Table Compression for details on table compression using Oracle Data Pump import on Autonomous Database.

   For detailed information on Oracle Data Pump Import parameters see Oracle Database Utilities.

Oracle Data Pump supports importing data pump files into your Autonomous Database using an Oracle Cloud Infrastructure resource principal as a credential object.

1. Configure the dynamic groups and policies and enable Oracle Cloud Infrastructure resource principal to access the Object Store location where the data pump files you want to import reside.

   See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

2. Run Data Pump Import with the dumpfile parameter set to the list of file URLs on your Cloud Object Storage and the credential parameter set to OCI$RESOURCE_PRINCIPAL.

   For example:

   impdp admin/password@db2022adb_high \       
        directory=data_pump_dir \ 
        credential= 'OCI$RESOURCE_PRINCIPAL' \ 
        dumpfile= https://namespace-string.objectstorage.us-ashburn-1.oci.customer-oci.com/n/namespace-string/b/bucketname/o/export%l.dmp \
        parallel=16 \
        encryption_pwd_prompt=yes \
        exclude=cluster,indextype,db_link

   Notes for Data Pump parameters:

   • If during the export with expdp you used the encryption_pwd_prompt=yes parameter, also use encryption_pwd_prompt=yes and input the same password at the impdp prompt that you specified during the export.

   • The dumpfile parameter supports the %L and %l wildcards in addition to the legacy %U and %u wildcards. For example, dumpfile=export%L.dmp. Use the %L or %l wildcard for exports from Oracle Database Release 12.2 and higher. This wildcard expands the dumpfile file name into a 3-digit to 10-digit, variable-width incrementing integer, starting at 100 and ending at 2147483646.

     Use the legacy %U or %u wildcard for exports from Oracle Database prior to Release 12.2. If you use this option and more than 99 dump files are needed, you must specify multiple dumpfile names, each with the %U or %u parameter.

   • If your bucket and data do not reside in a commercial (OC1) realm, where OCI Dedicated Endpoints are not supported, use the following URI format for dumpfile:

     https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname/o/filename

   • When you specify the dumpfile value as a pre-authenticated URL, the credential parameter is required and the value can be NULL.

     See Use Oracle Data Pump Import with a Pre-Authenticated URL for details on the steps required to use a pre-authenticated URL.

   For dumpfile, this example uses the recommended URI format using OCI Dedicated Endpoints for commercial realm (OC1). The namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Object Storage Dedicated Endpoints, Regions and Availability Domains, and Understanding Object Storage Namespaces for more information.

   For the dump file URL format for different Cloud Object Storage services, see DBMS_CLOUD URI Formats.

   In this example the following are excluded during the Data Pump Import:

   • Clusters

   • Indextypes

   • Database links

   To perform a full import or to import objects that are owned by other users, you need the DATAPUMP_CLOUD_IMP role.

   For information on disallowed objects in Autonomous Database, see SQL Commands.

   See Notes for Importing with Oracle Data Pump for additional notes for using Oracle Data Pump Import.

   For detailed information on Oracle Data Pump Import parameters see Oracle Database Utilities.

You can import data from Data Pump files into your Autonomous Database using Data Pump client versions 12.2.0.1 and earlier by setting the default_credential parameter.

1. Store your Cloud Object Storage credential using DBMS_CLOUD.CREATE_CREDENTIAL.

   For example, to create Oracle Cloud Infrastructure Auth Token credentials:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => 'adb_user@example.com',
       password => 'password'
     );
   END;
   /

   For more information on Oracle Cloud Infrastructure Auth Token authentication see CREATE_CREDENTIAL Procedure.

   For example, to create Oracle Cloud Infrastructure Signing Key based credentials:

   BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL (
          credential_name => 'DEF_CRED_NAME',
          user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
          tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
          private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
          fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
   END;
   /

   For more information on Oracle Cloud Infrastructure Signing Key based credentials see CREATE_CREDENTIAL Procedure.

   Supported credential types:

   • Data Pump import supports Oracle Cloud Infrastructure Auth Token based credentials and Oracle Cloud Infrastructure Signing Key based credentials.

     For more information on the credential types for Oracle Cloud Infrastructure Cloud Object Storage, see CREATE_CREDENTIAL Procedure.

   • Data Pump supports using an Oracle Cloud Infrastructure Object Storage pre-authenticated URL for the dumpfile. When you use a pre-authenticated URL, setting the DEFAULT_CREDENTIAL is required and the DEFAULT_CREDENTIAL value can be NULL. See Using Pre-Authenticated Requests for more information.

   • Data Pump supports using a resource principal credential with impdp.

2. Set the credential as the default credential for your Autonomous Database, as the ADMIN user. For example:

   ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME'

   The DEFAULT_CREDENTIAL can be an OCI Resource Principal. For example:

   ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'OCI$RESOURCE_PRINCIPAL'

   See Configure Policies and Roles to Access Resources for more information on resource principal based authentication.

   Note
   
   The DEFAULT_CREDENTIAL value cannot be an Azure service principal, Amazon Resource Name (ARN), or a Google service account.

   The DEFAULT_CREDENTIAL value can be set to NULL if you are using a pre-authenticated URL.

3. Run Data Pump Import with the dumpfile parameter set to the list of file URLs on your Cloud Object Storage, and set the default_credential keyword. For example:

   impdp admin/password@db2022adb_high \      
        directory=data_pump_dir \
        dumpfile=default_credential:https://namespace-string.objectstorage.us-ashburn-1.oci.customer-oci.com/n/namespace-string/b/bucketname/o/export%l.dmp \
        parallel=16 \
        encryption_pwd_prompt=yes \
        exclude=cluster,indextype,db_link
   

   Notes for the Data Pump parameters:

   • If during the export with expdp you used the encryption_pwd_prompt=yes parameter then use encryption_pwd_prompt=yes and input the same password at the impdp prompt that you specified during the export.

   • The dumpfile parameter supports the %L and %l wildcards in addition to the legacy %U and %u wildcards. For example, dumpfile=export%L.dmp. Use the %L or %l wildcard for exports from Oracle Database Release 12.2 and higher. This wildcard expands the dumpfile file name into a 3-digit to 10-digit, variable-width incrementing integer, starting at 100 and ending at 2147483646.

     Use the legacy %U or %u wildcard for exports from Oracle Database prior to Release 12.2. If you use this option and more than 99 dump files are needed, you must specify multiple dumpfile names, each with the %U or %u parameter.

   • If your bucket and data do not reside in a commercial (OC1) realm, where OCI Dedicated Endpoints are not supported, use the following URI format for dumpfile:

     https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname/o/filename

   • When you specify the dumpfile value as a pre-authenticated URL, setting the default_credential parameter is required and the value can be NULL.

     See Use Oracle Data Pump Import with a Pre-Authenticated URL for details on the steps required to use a pre-authenticated URL.

   For dumpfile, this example uses the recommended URI format using OCI Dedicated Endpoints for commercial realm (OC1). The namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Object Storage Dedicated Endpoints, Regions and Availability Domains, and Understanding Object Storage Namespaces for more information.

   For the dump file URL format for different Cloud Object Storage services, see DBMS_CLOUD URI Formats.

   In this example the following are excluded during the Data Pump Import:

   • Clusters

   • Indextypes

   • Database links

Provides the steps to use a pre-authenticated URL (PAR URL) with Oracle Data Pump Import.

1. Use Oracle Data Pump expdp and export to local storage.

   See Export Your Existing Oracle Database to a Local Database File (Export without Credential Parameter) for more information.

2. Upload the dumpfile(s) to Oracle Cloud Infrastructure Object Storage.

   For example use DBMS_CLOUD.PUT_OBJECT to upload the dumpfiles.

3. Create a pre-authenticated URL on Oracle Cloud Infrastructure Object Storage, either on the object store bucket if there are multiple dumpfiles or on the dumpfile, if there is only one file.

   See Object Storage Pre-Authenticated Requests for more information.

4. Import the dumpfile(s) with the dumpfile parameter value set to the pre-authenticated URL.

   See Import Data Using Oracle Data Pump Version 18.3 or Later for more information.