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

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

1. On the source database, export the SODA collection using the Oracle Data Pump expdp command.

   See Export Your Existing Oracle Database to Import into Autonomous AI Database for more information.

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 AI 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 AI Database where you import the collection, create a new collection:

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

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

   See SODA Default Collection Metadata on Autonomous AI 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 the credential value can be NULL. 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 AI 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 AI 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 AI 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 AI 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.