I export db from user which is related on tablespace USER on local PC and import to user which is related to tablespace DATATAB on server....my question is: will data be stored in DATATAB tablespace or USER tablespace on server? I was using standard imp command. I am afraid if it goes to USER tablespace I will not have enough space aafter time pass and db become big....please answer....just to mention that user on server on creation is related to DATATAB tablespace and version of oracle is 11gr2.....thank you


Why using original Export and Import utilities (imp and exp respectively), which are deprecated, while since 10g Oracle provides a new, more efficient facility called Data Pump?

You can use Data Pump Import with REMAP_TABLESPACE parameter to specify the new tablespace name for the objects being imported:

$ impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY=dpump_dir1 \

One of the things that would require you to use original Import is that you already have a file generated by original Export, and don't have access to the source database for using Data Pump Export, because as per Database Upgrade Guide:

Dump files generated by the Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility, and vice versa.

In which case you can (as seems to me) follow the Reorganizing Tablespaces section in Database Utilities, which describes the procedure:

If a user's quota allows it, the user's tables are imported into the same tablespace from which they were exported. However, if the tablespace no longer exists or the user does not have the necessary quota, then the system uses the default tablespace for that user[…]

Basically you need to set the user's default tablespace during import to one which should contain the imported objects.

SQL> alter user spongebob default tablespace newtbs;

Where newtbs is the name of the new tablespace.

See also:

  • My user on server is related to correct tablespace "DATTATAB" but tables for that user are related to USER tablespace....tables contain indexes and constraints.....How can I now change for tables to go to DATATAB tablespace??? – user2722863 Sep 3 '14 at 13:48
  • Did you try to set quota to 0 on USER tablespace before importing data? For example, alter user YOURUSERNAME quota 0 on tablespace users. If the user doesn't have the necessary quota on the tablespace where the data should be imported from the dump file, then the data is imported in default tablespace set for the user, e. g. alter user YOURUSERNAME default tablespace newtbs. Check this out docs.oracle.com/cd/E11882_01/server.112/e22490/… – Yasir Arsanukaev Sep 6 '14 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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