Oracle – Importing to a different tablespace

If all the tables are in one tablespace, and you just want to import to another user and a different tablespace, all you need to do is make sure the default tablespace of the new user is set to the new tablespace and that they don't have unlimited tablespace privileges. If we're exporting from test_one and importing to test_two and the tablespaces are the same as the user names, then:

revoke unlimited tablespace from test_two; alter user test_two default tablespace test_two quota unlimited on test_two quota 0 on test_one;

This forces the imp utility to create the tables in the default tablespace. Just do a normal exp then imp with fromuser & touser.

Here's a test script to prove it:

create tablespace test_one datafile '/apps/oracle/oradata/acs816/test_one.dbf' size 4M; create tablespace test_two datafile '/apps/oracle/oradata/acs816/test_two.dbf' size 4M; create user test_one identified by test_one default tablespace test_one; create user test_two identified by test_two default tablespace test_two; grant create session to test_one; grant create session to test_two; grant create table to test_one; grant create table to test_two; alter user test_two quota 0 on test_one quota unlimited on test_two; revoke unlimited tablespace from test_two; connect test_one/test_one create table test (object_name varchar2(30)); insert into test (select object_name from all_objects);
exp test_one/test_one file=test_one.dmp
imp test_two/test_two file=test_one.dmp fromuser=test_one touser=test_two

Oct 2002