Import SODA Collection Data Using Oracle Data Pump Version 19.6 or Later

Shows the steps to import SODA collections into Autonomous Database with Oracle Data Pump.

You can export and import SODA collections using Oracle Data Pump Utilities starting with version 19.6. Oracle recommends using the latest Oracle Data Pump version for importing data from Data Pump files into your database.

Download the latest version of Oracle Instant Client, 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.

In Oracle Data Pump, if your source files reside on Oracle Cloud Infrastructure Object Storage you can use Oracle Cloud Infrastructure native URIs, Swift URIs, or pre-authenticated URIs. See DBMS_CLOUD Package File URI Formats for details on these file URI formats.

If you are using an Oracle Cloud Infrastructure pre-authenticated URI, you still need to supply a credential parameter. However, credentials for a pre-authenticated URL are ignored (and the supplied credentials do not need to be valid). See DBMS_CLOUD Package File URI Formats for information on Oracle Cloud Infrastructure pre-authenticated URIs.

This example shows how to create the SODA collection metadata and import a SODA collection with Data Pump.

  1. On the source database, export the SODA collection using the Oracle Data Pump expdp command.
  2. Upload the dump file set from Step 1 to Cloud Object Storage.
  3. Create a SODA collection with the required SODA collection metadata on your Autonomous Database.

    For example, if you export a collection named MyCollectionName from the source database with the following metadata:

    • The content column is a BLOB type.

    • The version column uses the SHA256 method.

    Then on the Autonomous Database where you import the collection, create a new collection:

    • By default on Autonomous Database for a new collection the content column is set to BLOB with the jsonFormat specified as OSON.

    • By default on Autonomous Database for a new collection the versionColumn.method is set to UUID.

    See SODA Default Collection Metadata on Autonomous Database for details.

    For example:

    
    DECLARE
       collection_create SODA_COLLECTION_T;
    BEGIN
       collection_create := DBMS_SODA.CREATE_COLLECTION('MyCollectionName');
    END;
    /
    COMMIT;

    You can use the PL/SQL function DBMS_SODA.LIST_COLLECTION_NAMES to discover existing collections. See LIST_COLLECTION_NAMES Function for more information.

    You can view the metadata for the SODA collections by querying the view USER_SODA_COLLECTIONS. See USER_SODA_COLLECTIONS for more information.

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

    For example, to create Oracle Cloud Infrastructure Auth Token credentials:

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

    For more information on Oracle Cloud Infrastructure Auth Token authentication see CREATE_CREDENTIAL Procedure.

    For example, to create Oracle Cloud Infrastructure Signing Key based credentials:

    BEGIN
       DBMS_CLOUD.CREATE_CREDENTIAL (
           credential_name => 'DEF_CRED_NAME',
           user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
           tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
           private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
           fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
    END;
    /

    For more information on Oracle Cloud Infrastructure Signing Key based credentials see CREATE_CREDENTIAL Procedure.

    Supported credential types:

    • Data Pump Import supports Oracle Cloud Infrastructure Auth Token based credentials and Oracle Cloud Infrastructure Signing Key based credentials.

      For more information on Oracle Cloud Infrastructure Signing Key based credentials see CREATE_CREDENTIAL Procedure.

    • Data Pump supports using an Oracle Cloud Infrastructure Object Storage pre-authenticated URL for the dumpfile parameter. When you use a pre-authenticated URL, providing the credential parameter is required and impdp ignores the credential parameter. When you use a pre-authenticated URL for the dumpfile, you can use a NULL value for the credential in the next step. See Using Pre-Authenticated Requests for more information.
  5. Run Data Pump Import with the dumpfile parameter set to the list of file URLs on your Cloud Object Storage and the credential parameter set to the name of the credential you created in the previous step.
    Note

    Import the collection data using the option CONTENT=DATA_ONLY.

    Specify the collection you want to import using the INCLUDE parameter. This is useful if a data file set contains the entire schema and the SODA collection you need to import is included as part of the dump file set.

    Use REMAP_DATA to change any of the columns during import. This example shows using REMAP_DATA to change the version column method from SHA256 to UUID.

    impdp admin/password@db2022adb_high \       
         directory=data_pump_dir \       
         credential=def_cred_name \      
         dumpfile= https://namespace-string.objectstorage.us-ashburn-1.oci.customer-oci.com/n/namespace-string/b/bucketname/o/export%l.dmp \
         
         encryption_pwd_prompt=yes \
         SCHEMA=my_schema \
         INCLUDE=TABLE:\"= \'MyCollectionName\'\"  \
         CONTENT=DATA_ONLY \
         REMAP_DATA=my_schema.'\"MyCollectionName\"'.VERSION:SYS.DBMS_SODA.TO_UUID 
          
    

    Notes for Data Pump parameters:

    • If during the export with expdp you used the encryption_pwd_prompt=yes parameter then use encryption_pwd_prompt=yes and input the same password at the impdp prompt that you specified during the export.

    • 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.

      Use the legacy %U or %u wildcard for exports from Oracle Database prior to Release 12.2. If you use this option and more than 99 dump files are needed, you must specify multiple dumpfile names, each with the %U or %u parameter.

    For dumpfile, this example uses the recommended URI format using OCI Dedicated Endpoints for commercial realm (OC1). The namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Object Storage Dedicated Endpoints, Regions and Availability Domains, and Understanding Object Storage Namespaces for more information.

    In Oracle Data Pump version 19.6 and later, the credential argument authenticates Oracle Data Pump to the Cloud Object Storage service you are using for your source files. The credential parameter cannot be an Azure service principal, Amazon Resource Name (ARN), or a Google service account. See Accessing Cloud Resources by Configuring Policies and Roles for more information on resource principal based authentication.

    The dumpfile argument is a comma delimited list of URLs for your Data Pump files.

    For the best import performance use the HIGH database service for your import 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 Import, see Manage Concurrency and Priorities on Autonomous Database.

    For the dump file URL format for different Cloud Object Storage services, see DBMS_CLOUD Package File URI Formats.

    Note

    To perform a full import or to import objects that are owned by other users, you need the DATAPUMP_CLOUD_IMP role.

    For information on disallowed objects in Autonomous Database, see SQL Commands.

    In this import example, the specification for the REMAP_DATA parameter uses the function DBMS_SODA.TO_UUID to generate UUID values. By default, for on-premise databases, the version column of a SODA collection is computed using SHA-256 hash of the document's content. On Autonomous Database the version column uses UUID generated values, which are independent of the document's content.

    In this example the REMAP_DATA parameter uses the DBMS_SODA.TO_UUID function to replace the source collection version type with UUID versioning. If in the export dump file set that you are importing the versionColumn.method is already set to UUID, then the REMAP_DATA for this field is not required.

    For detailed information on Oracle Data Pump Import parameters see Oracle Database Utilities.

The log files for Data Pump Import operations are stored in the directory you specify with the Data Pump Import DIRECTORY parameter. See Accessing the Log File for Data Pump Import for more information.