Move Data to Object Store as JSON Data Using EXPORT_DATA

Use DBMS_CLOUD.EXPORT_DATA to export data from an Autonomous Database as JSON.

Export JSON Data to Cloud Object Storage

Shows the steps to export table data from your Autonomous Database to Cloud Object Storage as JSON data by specifying a query.

This export method supports all the Cloud Object Stores supported by Autonomous Database, and you can use an Oracle Cloud Infrastructure resource principal to access your Oracle Cloud Infrastructure Object Store or Amazon Resource Names (ARNs) to access AWS Simple Storage Service (S3).

  1. Connect to your Autonomous Database instance.

    See Connecting to Autonomous Database for more information.

  2. Store your Cloud Object Storage credential using DBMS_CLOUD.CREATE_CREDENTIAL.

    For example:

    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'user1@example.com',
        password => 'password'
      );
    END;
    /

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

    Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

    Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

  3. Run DBMS_CLOUD.EXPORT_DATA and specify the format parameter type with the value json to export the results as JSON files on Cloud Object Storage.
    To generate the JSON output files there are two options for the file_url_list parameter:
    • Set the file_url_list value to the URL for an existing bucket on your Cloud Object Storage.

    • Set the file_url_list value to the URL for an existing bucket on your Cloud Object Storage and include a file name prefix to use when generating the file names for the exported JSON.

    If you do not include the file name prefix in the file_url_list, DBMS_CLOUD.EXPORT_DATA supplies a file name prefix. See EXPORT_DATA File Naming with JSON Output for details.

    For example, the following shows DBMS_CLOUD.EXPORT_DATA with a file name prefix specified in file_uri_list:

    BEGIN
      DBMS_CLOUD.EXPORT_DATA(
        credential_name => 'DEF_CRED_NAME',
        file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/dept_export',
        query           => 'SELECT * FROM DEPT',
        format          => JSON_OBJECT('type' value 'json'));
    END;
    /

    In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

    For detailed information about the parameters, see EXPORT_DATA Procedure.

Notes for exporting with DBMS_CLOUD.EXPORT_DATA:

  • The query parameter that you supply can be an advanced query, if required, such as a query that includes joins or subqueries.

  • Specify the format parameter with the compression option to compress the output files.

  • When you no longer need the files that you export, use the procedure DBMS_CLOUD.DELETE_OBJECT or use native Cloud Object Storage commands to delete the files.

EXPORT_DATA File Naming with JSON Output

Describes the file naming and output files for DBMS_CLOUD.EXPORT_DATA JSON results.

DBMS_CLOUD.EXPORT_DATA performs the query specified with the query parameter and sends the results to JSON files on Object Store. To speed up the procedure and to generate the output as fast as possible, DBMS_CLOUD.EXPORT_DATA divides its work. This means that, depending on system resources, when you run DBMS_CLOUD.EXPORT_DATA the procedure creates multiple output files in the Cloud Object Store bucket.

The format for each generated JSON file is:

[FileNamePrefix | client_info_module_action]_sequenceNum_timestamp.format_extension.[compression_extension]

  • FileNamePrefix: (optional) If a FileNamePrefix is supplied, DBMS_CLOUD.EXPORT_DATA uses the file name prefix to generate file names for the JSON results. The FileNamePrefix is specified using the text supplied after the bucket name in the file_uri_list parameter value.

    You cannot provide multiple values for the FileNamePrefix in the file_uri_list.

  • client_info_module_action: If a file name prefix is not supplied with the file_uri_list parameter, DBMS_CLOUD.EXPORT_DATA uses the combination of client_info, application module and action as the file name prefix (when this information is available). The procedure obtains these names from the application information for the database session that runs the query. See DBMS_APPLICATION_INFO for information on client_info, module name, and action name.

    If a file name prefix is not supplied with the file_uri_list and the database session attributes are not available, DBMS_CLOUD.EXPORT_DATA uses the file name prefix "data".

  • sequenceNum: The sequence number associated with the DBMS_CLOUD.EXPORT_DATA query. Depending on the query, the database service, and the number of OCPUs in the Autonomous Database instance, there are one or more sequenceNums. Also, depending on the size of the results, there are one or more output files for each sequenceNum.

    See Manage Concurrency and Priorities on Autonomous Database for information on database services.

  • timestamp: Timestamp when the file is uploaded.

  • format_extension: This is always "json".

  • compression_extension: When you include the format parameter with the compression option with the value gzip, this is "gz".

For example, the file name prefix in the following DBMS_CLOUD.EXPORT_DATA procedure is specified in the file_url_list parameter, as dept_export.

BEGIN
  DBMS_CLOUD.EXPORT_DATA(
    credential_name => 'DEF_CRED_NAME',
    file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/dept_export',
    query           => 'SELECT * FROM DEPT',
    format          => JSON_OBJECT('type' value 'json'));
END;
/

When you specify a file name prefix the generated output files include the file name prefix, similar to the following:

dept_export_1_20210809T173033Z.json
dept_export_2_20210809T173034Z.json
dept_export_3_20210809T173041Z.json
dept_export_4_20210809T173035Z.json

The number of generated output files depends on the size of the results, the database service, and the number of OCPUs in the Autonomous Database instance.

In the following example the file_url_list parameter does not include a file name prefix and the compression parameter is supplied, with value gzip:

BEGIN
  DBMS_CLOUD.EXPORT_DATA(
    credential_name => 'DEF_CRED_NAME',
    file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/',
    query           => 'SELECT * FROM DEPT',
    format          => json_object('type' value 'json', 'compression' value 'gzip'));
END;
/

When a file name prefix is not in the file_uri_list parameter, DBMS_CLOUD.EXPORT_DATA uses a file name prefix of the form: client_info_module_action. For this example the generated output files include the file name prefix that DBMS_CLOUD.EXPORT_DATA supplies and the files are compressed with gzip and the file extension .gz is added, as follows:

Client1_Module1_Action1_1_20210809T173033Z.json.gz
Client1_Module1_Action1_2_20210809T173034Z.json.gz
Client1_Module1_Action1_3_20210809T173041Z.json.gz
Client1_Module1_Action1_4_20210809T173035Z.json.gz

If the client_info_module_action session information is not available when you run DBMS_CLOUD.EXPORT_DATA, the file name prefix is set to data. For example:

data_1_20210809T173033Z.json.gz
data_2_20210809T173034Z.json.gz
data_3_20210809T173041Z.json.gz
data_4_20210809T173035Z.json.gz

Notes for JSON file naming with DBMS_CLOUD.EXPORT_DATA:

  • DBMS_CLOUD.EXPORT_DATA does not create buckets.

  • The number of JSON files that DBMS_CLOUD.EXPORT_DATA generates is determined by the number of OCPUs, the database service, and the size of the result data.

  • When a generated file contains 10MB of JSON data, a new output file is created. However, if you have less than 10MB of JSON result data you may have multiple JSON output files, depending on the database service and the number of OCPUs for the Autonomous Database instance.