Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated?

As an example, I have exported BLOG_DATA from OLDDB, where all user data is stored in the USERS tablespace.

On NEWDB, I would like to import the BLOG_DATA schema, but store the user objects in the BLOG_DATA tablespace, created specifically for this user.

I have created the BLOG_DATA user, created the BLOG_DATA tablespace and set it as the default tablespace for that user and added an appropriate unlimited quota.

CREATE TABLESPACE blog_data DATAFILE SIZE 1G;

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

GRANT connect,resource TO blog_data

The schema was exported from OLDDB with something like

exp blog_data/secretpassword@OLDDB file=blog_data.dmp 

After reading Phil's excellent answer below, I found myself wondering:

Since data has no other place to go than the default tablespace - the only tablespace the user has a quota on - will this effectively force imp to put all user objects in that default tablespace?

imp blog_data/secretpassword@NEWDB file=blog_data.dmp

Would this then place the entire blog_data schema in the blog_data tablespace on NEWDB? Is there any reason why this wouldn't work or that I would run into problems with certain objects etc?

update:

I did a quick test and found this to be the case. Imp places objects in the default tablespace for that user, provided it can't place it in the original tablespace (eg. the tablespace does not exist). Full explanation: http://www.dolicapax.org/?p=57

Still, I suppose that using Data Pump like Phil suggests might be the preferred option.

share|improve this question
    
Was it exported using the legacy exp utility or with expdp (data pump)? –  FreshPhilOfSO Oct 12 '12 at 13:57
    
In the example it was exported with the conventional exp utility, as shown above. I suppose one could just as easily use expdp and pick up the file from the old db server, would that make the transition to a new tablespace any easier? –  Roy Oct 12 '12 at 14:06
    
I've covered both in my answer. Use data pump - it's easier to perform this task –  FreshPhilOfSO Oct 12 '12 at 14:07
add comment

1 Answer

up vote 6 down vote accepted

It's not actually possible to specify a different tablespace when importing using the oracle imp utility. However, as a workaround, you can pre-create the tables by doing a ROWS=N import into the USERS tablespace, then alter table mytable move tablespace BLOG_DATA; for each table to move them to the new tablespace, then do the import again with the IGNORE=Y parameter to ignore the table creation errors and import all of the data.

If the data was exported using Data Pump (expdp), (as an aside, everyone should be using this these days, rather than the old legacy exp/imp utilities) you can easily import into another tablespace using the REMAP_TABLESPACE parameter.

eg:

impdp scott/tiger@ZOMG file=blog_data.dmp directory=mydir remap_tablespace=USERS:BLOG_DATA
share|improve this answer
    
Thank you very much, Phil. I was wondering: If the user being imported to exclusively has a quota on the default tablespace, will that force imp to place all imported objects in that default tablespace regardless of the layout on the source of the data? –  Roy Oct 12 '12 at 14:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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