Occasionally I find myself migrating Oracle databases between environments using Data Pump.

Enterprise applications tend to use multiple tablespaces for logical separation (and historical reasons). However, this isn’t possible when the target database is Autonomous Database (ADB) hosted on Oracle Cloud Infrastructure (OCI).

ADB is a managed service so DBA’s can’t create tablespaces and are limited to ‘DATA’.

However, Oracle 19c Data Pump supports wildcards for the REMAP_TABLESPACE parameter which allows multiple tablespaces from the existing application to be mapped to the ADB DATA tablespace.

#!/bin/bash

for SCHEMA in MYSCHEMA
do
  PARFILE=/tmp/dpexp_$$.par

  cat > ${PARFILE} <<EOF
schemas=${SCHEMA}
logfile=imp_schema_${SCHEMA}.log
directory=TEST_DIR
dumpfile=exp_schema_${SCHEMA}.dmp
transform=disable_archive_logging:Y
table_exists_action=REPLACE
remap_tablespace=%:DATA
metrics=Y
logtime=ALL
EOF

  cat ${PARFILE}
  impdp user/pass@DB parfile=${PARFILE}
done

More info - Oracle 19C Data Pump documentation