doli capax

In the old days, it was common to store objects from assorted users in the USERS tablespace. In more modern days, it sometimes seems more appropriate to store user objects in a tablespace dedicated to that schema.

So, as I was transferring data to a new Oracle server, I found myself wishing to import data into a different tablespace than on the source database.

While the impdp tool includes a remap option to take care of such a scenario, the traditional imp tool does not. A workaround is to force all user objects into the default tablespace of that user, however this only works if the source tablespace does not exist.

As an example, I have a user blog_data on OLDDB with it’s objects (in this case, a single table with a single row, and the associated primary key index) stored in the users tablespace.

I’ll export the schema using something like:

exp blog_data/secret@OLDDB file=blog_data.dmp

Then on the destination database, I need to create a user with appropriate privileges, default tablespace and make sure the USERS tablespace does not exist. Assuming this is a new installation, I can simply set a new default permanent tablespace for the database and delete the empty “USERS” tablespace:

CREATE TABLESPACE users2 DATAFILE SIZE 100M;

ALTER DATABASE DEFAULT TABLESPACE users2;

DROP TABLESPACE USERS;

CREATE TABLESPACE blog_data DATAFILE SIZE 1M;

CREATE USER blog_data IDENTIFIED BY secretpassword DEFAULT TABLESPACE blog_data QUOTA UNLIMITED ON blog_data;

GRANT connect,resource TO blog_data;

Now I can simply import the schema into my new user, forcing imp to place all data in the default tablespace:

[oracle@oratest1 impexptest]$ imp blog_data/secretpassword@NEWDB file=blog_data.dmp

Import: Release 11.2.0.3.0 – Production on Fri Oct 12 23:15:44 2012

…

Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing BLOG_DATA’s objects into BLOG_DATA
. . importing table “PEOPLE” 1 rows imported
Import terminated successfully without warnings.

The import completes without warnings, and we can then see that the table is in the right place:

[oracle@oratest1 impexptest]$ sqlplus “blog_data/secretpassword”

…

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
—————————— ——————————
PEOPLE BLOG_DATA