Oracle Cloud Infrastructure Documentation

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.

  1. Connect to your Autonomous AI Database instance.
  2. Create a directory.

    For example:

    CREATE DIRECTORY export_dir AS 'export_dir';
  3. Export data from Autonomous AI Database to your directory as Oracle Data Pump dump file(s) with DBMS_CLOUD.EXPORT_DATA and specify the format parameter type as datapump. For example:
    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => 'export_dir:sales.dmp',
    format => json_object('type' value 'datapump'),
    query => 'SELECT * FROM sales'
 );
END;
/

    Example to export data as multiple Data Pump files to a directory:

    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => 'export_dir:sales1.dmp, export_dir:sales2.dmp',
    format => json_object('type' value 'datapump'),
    query => 'SELECT * FROM sales'
 );
END;
/

    The parameters are:

    • file_uri_list: is a comma delimited list of the export file(s). Use of wildcard and substitution characters is not supported in the file_uri_list.

    • format: specifies the required type parameter. The valid values are datapump, json, xml, csv and parquet and it also optionally defines the options you can specify for the export with the ORACLE_DATAPUMP Access Driver.

    • query: specifies a SELECT statement so that only the required data is exported. The query determines the contents of the dump file(s).

    Note

    The DBMS_CLOUD.EXPORT_DATA procedure creates the dump file(s) that you specify in the file_uri_list.

    For detailed information about the parameters, see EXPORT_DATA Procedure.

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 have WRITE 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 the file_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 AI 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 AI Database instance with the medium or high 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 Pump impdp. Depending on the database, you can use these files as follows:

    • On an Autonomous AI Database, you can use the dump files with the DBMS_CLOUD procedures that support the format parameter type with the value 'datapump'. You can import the dump files using DBMS_CLOUD.COPY_DATA or you can call DBMS_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 the ORACLE_DATAPUMP access driver. See Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver for more information.