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