How do I move JSON collections from one Oracle Autonomous Database to another?

Question

I've got some JSON collections in the Autonomous Database (ATP/AJD) and I'd like to copy them to another instance.

Answer 1

If you are using the Oracle API for MongoDB, you always have the option to use mongoexport/mongoimport or mongodump/mongorestore. https://www.mongodb.com/docs/database-tools/mongodump/

Otherwise, there are two options.

The first option is that you can use DBMS_CLOUD.EXPORT_DATA and DBMS_CLOUD.CREATE_EXTERNAL_TABLE

On the source database use EXPORT_DATA to copy the backing table to object storage:

begin
DBMS_CLOUD.EXPORT_DATA (
      file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/tenant/b/mybucket/o/out.dmp',
      format => '{"type":"datapump"}',
      credential_name => 'DEF_CRED_NAME',
      query => 'SELECT * FROM MYCOLLECTION');
end;
/

Then, on the target database, use CREATE_EXTERNAL_TABLE to map the data in:

begin
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'MYCOLLECTION_EXT',
    credential_name =>'DEF_CRED_NAME',
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/tenant/b/mybucket/o/out.dmp',
    format => json_object('type' value 'datapump', 'rejectlimit' value '1'),
    column_list => 'ID VARCHAR2(255 byte),
                    CREATED_ON TIMESTAMP(6),
                    LAST_MODIFIED TIMESTAMP(6),
                    VERSION VARCHAR2(255 byte),
                    DATA BLOB');
END;
/

Create an empty collection (e.g. mycollection) and load it using insert as select:

INSERT /*+ PARALLEL */ INTO NAVTNAHISTORY2 (ID, CREATED_ON, LAST_MODIFIED, VERSION, DATA)
SELECT /*+ PARALLEL */ ID, CREATED_ON, LAST_MODIFIED, VERSION, DATA FROM NAVTNAHISTORY_EXT;

Use the "high" connection to support parallel loading.

See: https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/dbms-cloud-package.html

The second option is that you can use impdp/expdp. First export the collection table to object storage:

expdp 'admin/xxxx@mydb_high' \
     credential=DEF_CRED_NAME \
     filesize=5GB \
     dumpfile=https://objectstorage.us-phoenix-1.oraclecloud.com/n/tenant/b/mybucket/o/out%u.dmp \
     parallel=16 \
     logfile=export.log \
     directory=data_pump_dir \
     TABLES=MYCOLLECTION

Create an empty collection (e.g. mycollection) and then loaded it using impdp

impdp 'admin/xxxx@mydb2_high' \
       credential=DEF_CRED_NAME \
       DUMPFILE=https://objectstorage.us-phoenix-1.oraclecloud.com/n/tenant/b/mybucket/o/out%u.dmp \
       TABLES=MYCOLLECTION \
       CONTENT=DATA_ONLY \
       parallel=16

See: https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/export-data-create-dump-file.html#GUID-8D734C1A-FAF3-446C-B777-16DF62FB049E