Shows how to use Oracle Data Pump to export data from Autonomous Database to Cloud Object Store directly.

1. Connect to your Autonomous Database.
2. Store your Cloud Object Storage credential using the DBMS_CREDENTIAL.CREATE_CREDENTIAL procedure. For example:

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

   The values you provide for username and password depend on the Cloud Object Storage service you are using:

   • Oracle Cloud Infrastructure Object Storage: username is your Oracle Cloud Infrastructure user name and password is your Oracle Cloud Infrastructure auth token. See Working with Auth Tokens.
   • Oracle Cloud Infrastructure Object Storage Classic: username is your Oracle Cloud Infrastructure Classic user name and password is your Oracle Cloud Infrastructure Classic password.

   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 all data loads.

3. As an ADMIN user, set the credential you defined in the above step as the default credential for your Autonomous Database.
   For example:

   ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'DEF_CRED_NAME';

4. Run Data Pump Export with the dumpfile parameter set to the URL for an existing bucket on your Cloud Object Storage (ending with a file name or a file name with a substitution variable, such as exp%U.dmp).
   • Oracle Data Pump version 19.9 or later:
     set the credential parameter to the name of the credential you created in Step 2. For example:

     expdp admin/password@ADBD_high \
     SCHEMAS=SOE3 \
     filesize=5GB \
     credential=DEF_CRED_NAME \
     dumpfile=https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/adbdpreview1/mybucket/export%L.dmp \
     parallel=16 \
     encryption_pwd_prompt=yes \
     logfile=export.log \
     directory=data_pump_dir \
     EXCLUDE=statistics,index

     In this example, dumpfile is an Oracle Cloud Infrastructure Swift URI that specifies all files whose name matches export<number>.dmp in the mybucket bucket in the us-ashburn-1 region. (adbdpreview1 is the object storage namespace in which the bucket resides.)
   • Oracle Data Pump versions earlier than 19.9:
     start the value of the dumpfile parameter with the default_credential keyword and a colon. For example:

     expdp admin/password@ADBD_high \
     SCHEMAS=SOE3 \
     filesize=5GB \
     dumpfile=DEF_CRED_NAME:https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/adbdpreview1/mybucket/export%L.dmp \ 
     parallel=16 \
     encryption_pwd_prompt=yes \
     logfile=export.log \
     directory=data_pump_dir \
     EXCLUDE=statistics,index 

     In this example, the default_credential keyword in the dumpfile parameter is required.
   Notes for Oracle Data Pump parameters:

   • By default, Oracle Data Pump Export compresses only metadata before writing to the dump file set. You can set the compression parameter to ALL to compress metadata and data before writing to the dump file set. See Oracle Database Utilities for more details.

   • 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.

   • For the best export performance use the high database service for your export connection and set the parallel parameter to the number of OCPUs or 0.25 times the number of ECPUs in your Autonomous Database. For information on which database service name to connect to run Data Pump Export, see Predefined Database Service Names for Autonomous Databases.

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

   For information on disallowed objects in Autonomous Database, see Limitations on the Use of SQL Commands.

   For detailed information on Oracle Data Pump Export parameters, see Oracle Data Pump Export in Oracle Database Utilities.

5. Validate the results.
   Oracle Data Pump divides each dump file part into smaller chunks for faster uploads. The Oracle Cloud Infrastructure Object Storage console shows multiple files for each dump file part that you export. The size of the actual dump files will be displayed as zero (0) and its related file chunks as 10mb or less. For example:

   exp01.dmp
   exp01.dmp_aaaaaa
   exp02.dmp
   exp02.dmp_aaaaaa

   Note
   
   Downloading the zero byte dump file from the Oracle Cloud Infrastructure console or using the Oracle Cloud Infrastructure CLI will not give you the full dump files. To download the full dump files from the Object Store, use a tool that supports Swift such as curl, and provide your user login and Swift auth token. For example:

   curl -O -v -X GET -u 'user1@example.com:auth_token' \
      https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/namespace-string/bucketname/export01.dmp

If required, download the dump files from Cloud Object Store and use Oracle Data Pump Import to import the dump file set to the target database. Then perform any required clean up.

1. Download the dump files from Cloud Object Store.
   Note
   
   This step is not needed if you are importing the data to another Autonomous Database.

   If you export directly to Object Store using Oracle Data Pump, as shown in Export Data From Autonomous Database to Object Store Directly, then the dump files on Object Store show size 0. Oracle Data Pump divides each dump file part into smaller chunks for faster uploads. The Oracle Cloud Infrastructure Object Storage console shows multiple files for each dump file part that you export. The size of the actual dump files will be displayed as zero (0) and its related file chunks as 10mb or less. For example:

   exp01.dmp
   exp01.dmp_aaaaaa
   exp02.dmp
   exp02.dmp_aaaaaa

   Downloading the zero byte dump file from the Oracle Cloud Infrastructure console or using the Oracle Cloud Infrastructure CLI will not give you the full dump files. To download the full dump files from the Object Store, use a tool that supports Swift such as curl, and provide your user login and Swift auth token. For example:

   curl -O -v -X GET -u 'user1@example.com:auth_token' \
      https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/namespace-string/bucketname/exp01.dmp

   The cURL command does not support wildcards or substitution characters in its URL. You need to use multiple cURL commands to download the dump file set from your Object Store. Alternatively, you can use a script that supports substitution characters to download all the dump files from your Object Store in a single command. See How To: Download all files from an export to object store job in Autonomous Database using cURL for an example.

2. Run Data Pump Import to import the dump file set to the target database.
   Note
   
   

   If you are importing the data to another Autonomous Database, see Load Data Using Oracle Data Pump.

   In case of files exported directly to Object Store using Oracle Data Pump, as shown in Export Data From Autonomous Database to Object Store Directly, if you import a file with the DBMS_CLOUD procedures that support the format parameter type with the value 'datapump', you only need to provide the primary file name. The procedures that support the 'datapump' format type automatically discover and download the chunks.

3. Perform post import clean up tasks. If you are done importing the dump files to your target database(s) then drop the bucket containing the data or remove the dump files from the Cloud Object Store bucket, and remove the dump files from the location where you downloaded the dump files to run Data Pump Import.