Oracle Cloud Infrastructure Documentation

Use Data Pump to Create a Dump File Set on Autonomous Database

Shows the steps to export data from your Autonomous Database to a directory with Oracle Data Pump.

Oracle recommends using the latest Oracle Data Pump version for exporting data from Autonomous 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. 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. See Oracle Instant Client Downloads for details.

Note

Database Actions provides a link for Oracle Instant Client. To access this link from Database Actions, under Downloads, click Download Oracle Instant Client.
  1. Run Data Pump Export with the dumpfile parameter set, the filesize parameter set to less than 5G, and the directory parameter set. For example, the following shows how to export a schema named SALES in an Autonomous Database named DB2022ADB with 16 ECPUs:
    expdp sales/password@db2022adb_high 
directory=data_pump_dir 
dumpfile=exp%L.dmp 
parallel=4
encryption_pwd_prompt=yes
filesize=1G
logfile=export.log

    Notes for Data Pump parameters:

    • If during the export with expdp you use the encryption_pwd_prompt=yes parameter then 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 one quarter the number of ECPUs (.25 x ECPU count). If you are using OCPU compute model, set the parallel parameter to the number of OCPUs (1 x OCPU count). For information on which database service name to connect to run Data Pump Export, see Manage Concurrency and Priorities on Autonomous Database.

    After the export is finished you can see the generated dump files by running the following query:

    SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_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...
  2. Move the dump file set to your cloud object store. See Move Dump File Set from Autonomous Database to Your Cloud Object Store for details.
Note

  • To perform a full export or to export objects that are owned by other users, you need the DATAPUMP_CLOUD_EXP role.

  • The DATA_PUMP_DIR is the only predefined directory. You can specify a different directory as the directory argument if you previously created the directory and you have write privileges on the directory. See Create Directory in Autonomous Database for information on creating directories.

  • The API you use to move the dump files to Cloud Object Storage has a maximum file transfer size, so make sure you use a filesize argument that is less than or equal to the maximum supported size for your Cloud Object Storage service. See PUT_OBJECT Procedure for the Cloud Object Storage Service file transfer size limits.

  • For more information on Oracle Data Pump Export see Oracle Database Utilities.