13

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.

Improve this question
3
  • Was it exported using the legacy exp utility or with expdp (data pump)?
    – Philᵀᴹ
    Commented Oct 12, 2012 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
    Commented Oct 12, 2012 at 14:06
  • I've covered both in my answer. Use data pump - it's easier to perform this task
    – Philᵀᴹ
    Commented Oct 12, 2012 at 14:07

2 Answers 2

Reset to default
15

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
Improve this answer
2
  • 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
    Commented Oct 12, 2012 at 14:10
  • I'm working on something similar but it's a whole DB migration using expdp/impdp because I want our QA env to match production. If I use remap_tablespace it would remap every schema into another single tablespace, however I think I can run the impdp command once per schema and using remap_tablespace. This should achieve the job.
    – Nicolas de Fontenay
    Commented Apr 8, 2019 at 16:10
0

You should follow the steps as below mentioned:-

  • Export the user
  • Perform import using the command
    imp system/manager file=export.dmp indexfile=newfile.sql
    This will import the data and save all definitions into newfile.sql.
  • Drop necessary objects.
  • Run the script newfile.sql after altering the tablespaces.
  • Import from the backup for the necessary objects.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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