Oracle's datapump between host and guestVM

The Problem

You want to load data from your host database into your VM database. Instead of theoretical instruction I will show you an example.

click on the pictures to enlarge

Export a user from a host database

On the host exists a 12c database INMEM with the well known EXAMPLE tablespace. I want to export the user OE (Order Entries) and import it into the pluggable database PDB1 inside the guestVM.

Use this parfile inmemexp.par

directory = DATA_PUMP_DIR
logfile = oeexp.log
dumpfile = oeexp.dmp
schemas = oe
exclude = statistics

export like that:

expdp system@inmem parfile=inmemexp.par

Copy the oeexp.dmp into the shared folder /develop

Import it into the guestVM PDB1

/develop is mounted as

none on /develop type vboxsf (rw,nodev,relatime)

Type vboxsf is unknown to Datapump. Hence we create in the PDB1 a directory COMMON as '/u01/app/oracle/oradata' and copy the dumpfile to this directory. We write our inmemimp.par

directory = common
logfile = oeimp.log
dumpfile = oeexp.dmp
REMAP_TABLESPACE=EXAMPLE:USERS

Call datapump inside the guestVM like that:

impdp system/vagrant@pdb1 parfile=inmemimp.par

The user OE and the tablespace EXAMPLE do not exist in the PDB1. No problem: datapump is so clever to create the user and remap the target tablespace to USERS.

Control the success

by a script or using EM Express resp. SqlDeveloper.

a) the script

SYS@CDB1>alter session set container = pdb1;
Session altered.
SYS@CDB1>@getOE.sql
SYS@CDB1>col owner for a6
SYS@CDB1>col TABLESPACE_NAME for a6
SYS@CDB1>col table_name for a36
SYS@CDB1>col created for a12
SYS@CDB1>set echo on
SYS@CDB1>select ao.owner, TABLESPACE_NAME, table_name,
2 to_char(created, 'DD.MM.YY HH24:MI'), num_rows
3 from all_tables at, all_objects ao
4 where TABLESPACE_NAME = 'USERS'
5 and object_type = 'TABLE'
6 and table_name = object_name;