Export Data Using Oracle Data Pump

Oracle Data Pump offers very fast bulk data and metadata movement between Autonomous AI Database and other Oracle databases.

Oracle recommends using the latest Oracle Data Pump version for exporting data from Autonomous AI Database to other Oracle databases, as it contains enhancements and fixes for a better experience. Download the latest version of Oracle Instant Client and download the Tools Package, which includes Oracle Data Pump, for your platform from Oracle Instant Client Downloads. See the installation instructions on the platform install download page for the installation steps required after you download Oracle Instant Client and the Tools Package.

To move data from an Autonomous AI Database to other Oracle databases using Oracle Data Pump, use one of the following options:

Option 1: Move Data with Data Pump Export to an Autonomous AI Database Directory

Use Oracle Data Pump to export the data to a directory on your database, and then move the data from the directory to Cloud Object Store. This method can be beneficial when you want to move data from your Autonomous AI Database to multiple target databases. Instead of exporting the data to multiple Cloud Object Stores, you can create a dump file set once and use it for multiple target databases.

Perform the following steps to move data from Autonomous AI Database by exporting it to a directory on your database:
1. Export data to a directory on Autonomous AI Database and move the dump file set from the directory to your Cloud Object Store. See Export Data Using a Directory on Autonomous AI Database.
2. Download the dump files from the Cloud Object Store, import the data into the target database, and clean up the Cloud Object Store. See Download Dump Files, Run Data Pump Import, and Clean Up Object Store.
Option 2: Move Data with Data Pump Export to Object Store

Using this export method you use Oracle Data Pump to directly export data to your object store. This export method is supported with Oracle Cloud Infrastructure Object Storage and Oracle Cloud Infrastructure Object Storage Classic. This avoids the overhead of creating a dump file set in a directory on your Autonomous AI Database and then moving the dump file set to the Cloud Object Store. When you plan to move data to a single target database, this method saves effort and speeds up the export process.

Perform the following steps to move data from Autonomous AI Database by exporting it directly to your object store:
1. Export data from Autonomous AI Database to a Cloud Object Store directly. See Export Data From Autonomous AI Database to Object Store Directly.
2. Download the dump files from the Cloud Object Store, import the data into the target database, and clean up the Cloud Object Store. See Download Dump Files, Run Data Pump Import, and Clean Up Object Store.

Export Data Using a Directory on Autonomous AI Database

To export data using a directory on Autonomous AI Database, you must first create a dump file set with the exported data on a directory in your database and then upload these files from your database directory to the Cloud Object Store.

Use Data Pump to Create a Dump File Set onAutonomous AI Database

Create a directory in which to store the dump files containing the exported data. For example:
```
 CREATE DIRECTORY data_export_dir as 'data_export';
```
Run Data Pump Export with the dumpfile parameter set, the filesize parameter set to less than 50G, and the directory parameter set. For example, the following shows how to export a schema named SALES in an Autonomous AI Database named ATPC1 with 64 ECPUs:
```
 expdp sales/password@ATPC1_high
 directory=data_export_dir
 dumpfile=exp%L.dmp
 parallel=16
 encryption_pwd_prompt=yes
 filesize=1G
 logfile=export.log
```
Notes for Data Pump parameters:
- In case of ECPUs, parallel must be set to 0.25 times the ECPU count. Hence, in the above example with 64 ECPUs, parallel is set to 0.25 x 64, that is, 16 with expdp.
- In case of OCPUs, parallel must be set to the same value as OCPU count. In the above example with 16 OCPUs, parallel will be set to 16, with expdp.
- If during the export with expdp you use the encryption_pwd_prompt=yes parameter, then you must use encryption_pwd_prompt=yes with your import and input the same password at the impdp prompt to decrypt the dump files (remember the password you supply with export). The maximum length of the encryption password is 128 bytes.
- 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 CPUs in your a database. For information on which database service name to connect to run Data Pump Export, see Predefined Database Service Names for Autonomous AI Databases.

After the export is finished you can see the generated dump files by running a query like the following:
```
 SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_EXPORT_DIR');
```
For example, the output from this query shows the generated dump files and the export log file:
```
 OBJECT_NAME                 BYTES  CHECKSUM                   CREATED          LAST_MODIFIED

 ---------------------- ---------- --------- ------------------------------- --------------------
 exp01.dmp                   12288               12-NOV-19 06.10.47.0 PM GMT       12-NOV-19...
 exp02.dmp                    8192               12-NOV-19 06.10.48.0 PM GMT       12-NOV-19...
 exp03.dmp                 1171456               12-NOV-19 06.10.48.0 PM GMT       12-NOV-19...
 exp04.dmp                  348160               12-NOV-19 06.10.48.0 PM GMT       12-NOV-19...
 export.log                   1663               12-NOV-19 06.10.50.0 PM GMT       12-NOV-19...
```

Note:

To perform a full export or to export objects that are owned by other users, you need the DATAPUMP_CLOUD_EXP role.
The API you use to move the dump files to your Object Storage supports file sizes up to 50GB, so make sure you do not specify sizes higher than that in the filesize argument.
For more information, see Oracle Data Pump Export in Oracle Database 19c Utilities or Oracle Database 26ai Utilities.

Move Dump File Set from Autonomous AI Databaseto Your Cloud Object Store

Connect to your Autonomous AI Database.
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.

Move the dump files from the Autonomous AI Database to your Cloud Object Store by calling DBMS_CLOUD.PUT_OBJECT.

For example:

 BEGIN
    DBMS_CLOUD.PUT_OBJECT(credential_name => 'DEF_CRED_NAME',
      object_uri => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/exp01.dmp',
      directory_name => 'DATA_EXPORT_DIR',
      file_name => 'exp01.dmp');
    DBMS_CLOUD.PUT_OBJECT(credential_name => 'DEF_CRED_NAME',
      object_uri => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/exp02.dmp',
      directory_name => 'DATA_EXPORT_DIR',
      file_name => 'exp02.dmp');
    DBMS_CLOUD.PUT_OBJECT(credential_name => 'DEF_CRED_NAME',
      object_uri => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/exp03.dmp',
      directory_name => 'DATA_EXPORT_DIR',
      file_name => 'exp03.dmp');
    DBMS_CLOUD.PUT_OBJECT(credential_name => 'DEF_CRED_NAME',
      object_uri => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/exp04.dmp',
      directory_name => 'DATA_EXPORT_DIR',
      file_name => 'exp04.dmp');
 END;
 /

See PUT_OBJECT Procedure for information on PUT_OBJECT.

Export Data From Autonomous AI Database to Object Store Directly

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

Connect to your Autonomous AI Database.
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.
As an ADMIN user, set the credential you defined in the above step as the default credential for your Autonomous AI Database.

For example:
```
 ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'DEF_CRED_NAME';
```
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 COMPRESSION in Oracle Database 19c Utilities or Oracle Database 26ai 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 more information, see Parameters Available in Data Pump Export Command-Line Mode in Oracle Database 19c Utilities or Oracle Database 26ai Utilities.
- For the best export performance use the high database service for your export connection and set the parallel parameter to 0.25 times the number of ECPUs or same as the number of OCPUs in your Autonomous AI Database. For information on which database service name to connect to run Data Pump Export, see Predefined Database Service Names for Autonomous AI 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 AI Database, see Limitations on the Use of SQL Commands.
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 console or using the 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
```

Download Dump Files, Run Data Pump Import, and Clean Up Object Store

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.

Download the dump files from Cloud Object Store.

Note: This step is not needed if you are importing the data to another Autonomous AI Database

If you export directly to Object Store using Oracle Data Pump, as shown in Export Data From Autonomous AI 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.
Run Data Pump Import to import the dump file set to the target database.

Note: If you are importing the data to another Autonomous AI 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 AI 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.
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.

For detailed information, see Parameters Available in Oracle Data Pump Import Command-Line Mode in Oracle Database 19c Utilities or Oracle Database 26ai Utilities.