0

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!

  • export is almost deprecated, as Balazs suggests use the datapump, faster, more versatile – kevinsky Jan 17 at 13:52
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.