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?
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.