Export Data to a Directory as Oracle Data Pump Files
You can export data to a directory as Oracle Data Pump dump files by specifying a query.
With this export method you use the DBMS_CLOUD.EXPORT_DATA
procedure to specify a query to select the data to export as a dump file to a directory.
Notes for exporting data with DBMS_CLOUD.EXPORT_DATA
:
-
The provided directory must exist and you must be logged in as the
ADMIN
user or haveWRITE
access to the directory. -
The procedure does not overwrite files. If a dump file in the
file_uri_list
exists,DBMS_CLOUD.EXPORT_DATA
reports an error such as:ORA-31641: unable to create dump file "/u02/exports/123.dmp" ORA-27038: created file already exists
-
DBMS_CLOUD.EXPORT_DATA
does not create directories. -
The directory name is case-sensitive when enclosed in double quotes.
-
The number of dump files that
DBMS_CLOUD.EXPORT_DATA
generates is determined when the procedure runs. The number of dump files that are generated depends on the number of file names you provide in thefile_uri_list
parameter, as well as on the number of ECPUs available to the instance, the service level, and the size of the data.For example, if you use a 2 ECPU Autonomous Database instance or the
low
service, then a single dump file is exported with no parallelism, even if you provide multiple file names. If you use an 8 ECPU Autonomous Database instance with themedium
orhigh
service, then the jobs can run in parallel and multiple dump files are exported if you provide multiple file names. -
The
query
parameter value that you supply can be an advanced query, if required, such as a query that includes joins or subqueries. -
The dump files you create with
DBMS_CLOUD.EXPORT_DATA
cannot be imported using Oracle Data Pumpimpdp
. Depending on the database, you can use these files as follows:-
On an Autonomous Database, you can use the dump files with the
DBMS_CLOUD
procedures that support theformat
parametertype
with the value 'datapump
'. You can import the dump files usingDBMS_CLOUD.COPY_DATA
or you can callDBMS_CLOUD.CREATE_EXTERNAL_TABLE
to create an external table. -
On any other Oracle Database, such as Oracle Database 19c on-premise, you can import the dump files created with the procedure
DBMS_CLOUD.EXPORT_DATA
using theORACLE_DATAPUMP
access driver. See Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver for more information.
-
Parent topic: Export Data to a Directory