Oracle tablespaces

oracle-11g-r2tablespaces

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

Best Answer

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 \
DUMPFILE=employees.dmp

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:

Related Question