In the old days, it was common to store objects from assorted users in the USERS tablespace. In more modern days, it sometimes seems more appropriate to store user objects in a tablespace dedicated to that schema.
So, as I was transferring data to a new Oracle server, I found myself wishing to import data into a different tablespace than on the source database.
While the impdp tool includes a remap option to take care of such a scenario, the traditional imp tool does not. A workaround is to force all user objects into the default tablespace of that user, however this only works if the source tablespace does not exist.
As an example, I have a user blog_data on OLDDB with it’s objects (in this case, a single table with a single row, and the associated primary key index) stored in the users tablespace.
I’ll export the schema using something like:
exp blog_data/secret@OLDDB file=blog_data.dmp
Then on the destination database, I need to create a user with appropriate privileges, default tablespace and make sure the USERS tablespace does not exist. Assuming this is a new installation, I can simply set a new default permanent tablespace for the database and delete the empty “USERS” tablespace:
CREATE TABLESPACE users2 DATAFILE SIZE 100M;
ALTER DATABASE DEFAULT TABLESPACE users2;
DROP TABLESPACE USERS;
CREATE TABLESPACE blog_data DATAFILE SIZE 1M;
CREATE USER blog_data IDENTIFIED BY secretpassword DEFAULT TABLESPACE blog_data QUOTA UNLIMITED ON blog_data;
GRANT connect,resource TO blog_data;
Now I can simply import the schema into my new user, forcing imp to place all data in the default tablespace:
[oracle@oratest1 impexptest]$ imp blog_data/secretpassword@NEWDB file=blog_data.dmp
Import: Release 18.104.22.168.0 – Production on Fri Oct 12 23:15:44 2012
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing BLOG_DATA’s objects into BLOG_DATA
. . importing table “PEOPLE” 1 rows imported
Import terminated successfully without warnings.
The import completes without warnings, and we can then see that the table is in the right place:
[oracle@oratest1 impexptest]$ sqlplus “blog_data/secretpassword”
SQL> select table_name,tablespace_name from user_tables;