Oracle12c: Objects attempting to import to original tablespace (despite wanting to remap them)

Question

I've exported a schema from a DB server (Oracle 12.2.0.1.0) using exp with the following command.

exp USERID=FOO/FOO2019 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.dmp" LOG=FOO.log

I now want to import the exact same database into the same environment but with a new user and using different tablespaces. So I created new tablespaces for the user (FOO2), created the user (FOO2). I set the default tablespace for the user to also be FOO2 and only gave the FOO2 user access to the new tablespace. However when I attempt to run the import using the following command.

imp USERID=FOO2/FOO2019 FROMUSER=FOO TOUSER=FOO2 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.DMP" LOG="FOO2_implog.log"

I'm getting errors as it's attempting to import the database into the original "FOO" tablespace (which doesn't have enough free space). I've tried a bunch of different things and I'm honestly just baffled now as to how I can get everything importing into my new tablespace.

Any help would be appreciated.

Thanks!

• kevinskio: export is almost deprecated, as Balazs suggests use the datapump, faster, more versatile

Answer 1

Original import does not have any option to remap tablespaces. The tablespace metadata comes from the dumpfile, that is why import wants to create them in the original tablespace.

As a workaround, you can manually pre-create the tables in the new tablespace then load data into them.

But forget original export and import, just use Data Pump.

In the database:

create directory foo_dir as 'D:\app\ORACLE\admin\orcl\dpdump';
grant read, write on directory foo_dir to foo;
grant read, write on directory foo_dir to foo2;

Export:

expdp foo/foo2019 directory=foo_dir dumpfile=foo.dmp logfile=foo.log

Import:

impdp foo2/foo2019 directory=foo_dir dumpfile=foo.dmp logfile=foo2_imp.log remap_schema=foo:foo2 remap_tablespace=foo:foo2