remapping tablespace in imp?

Usage of the old imp/exp utilities is now dying out rapidly due to the lack of new feature support and the massive speed difference that datapump offers. The only time it really should be used now is for migration of pre 10g databases up to more current versions.

One of the (often) annoying features missing from exp/imp is the ability to move objects from one tablespace to another during the import process using the REMAP_TABLESPACE option.

However there is a kind of a fix for this that works in a limited way (other than creating an indexfile and editing the code yourself).

First up lets create some simple tables in 9i and put them in 2 different tablespaces

 SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;
 Grant succeeded.

SQL> create table test.tab1(col1 number) tablespace system;

Table created.

SQL> create table test.tab2(col1 number) tablespace tools;

Table created.

Now we export them

exp / owner=test

Export: Release 9.2.0.4.0 - Production on Mon Aug 4 20:49:46 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                           TAB1          0 rows exported
. . exporting table                           TAB2          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Now we copy the dumpfile over to another server and try an imp to an indexfile

imp / fromuser=test touser=test indexfile=test.sql

Import: Release 12.1.0.1.0 - Production on Mon Aug 4 20:57:12 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by OSAORACLE, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses WE8ISO8859P1 character set (possible charset conversion)
. . skipping table "TAB1"

. . skipping table "TAB2"

Import terminated successfully without warnings.
# cat test.sql

REM  CREATE TABLE "TEST"."TAB1" ("COL1" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1
REM  MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
REM  BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS ;
REM  ... 0 rows
REM  CREATE TABLE "TEST"."TAB2" ("COL1" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1
REM  MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
REM  BUFFER_POOL DEFAULT) TABLESPACE "TOOLS" LOGGING NOCOMPRESS ;
REM  ... 0 rows

This confirms the create statements have the 2 hardcoded names we don't want to use

If these tablespace exist and we have quota on them imp will try and create the objects in the same tablespace as before - which we don't want. What we will do instead is not create the one tablespace at all and only let the test user have privileges on a new tablespace we create.

SQL> create tablespace tools_new;

Tablespace created.

SQL> create user test identified by test;

User created.

SQL> grant create table to test;

Grant succeeded.

SQL> alter user test quota unlimited on tools_new;

User altered.

SQL> alter user test default tablespace tools_new;

User altered.

Now import


imp / fromuser=test touser=test

Import: Release 12.1.0.1.0 - Production on Mon Aug 4 21:22:39 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by OSAORACLE, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing TEST's objects into TEST
. . importing table                         "TAB1"          0 rows imported
. . importing table                         "TAB2"          0 rows imported
Import terminated successfully without warnings.

So that worked fine - and just to confirm it did actually work

SQL> select table_name,tablespace_name from dba_tables where owner='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
TAB1
TOOLS_NEW

TAB2
TOOLS_NEW

You have to be careful though with what rights you give the use - if it has unlimited tablespace then it will try and create them in the original tablespace (if it exists) - also if you have the resource role in version prior to 12c this implicilty gives you unlimited tablespace and may catch you out.

I think there might also be issues with certain object types where tablespace clauses can be explicitly specified at a 'lower' level - clobs for instance.

It's useful in some cases though