Database Software

Hi all,

Here is my scenario:

I have a user USER_A which has data stored in some tablespaces - say q,w,e.
I would like to export all tabledata and import it to a new user USER_B. All data imported to B should be stored in tablespace USER_B_PERM.

I've tried a lot of things yet, but imp always tries to import the tables to the original tablespaces.

What I have done yet:


creating the new tablespace:

create tablespace USER_B_PERM
DATAFILE '/oradata_big/path/USER_B/USER_B_PERM.DBF' SIZE 126M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 125M;

ALTER DATABASE DATAFILE '/oradata_big/path/USER_B/USER_B_PERM.DBF'
AUTOEXTEND ON;


create temporary tablespace USER_B_TEMP
TEMPFILE '/oradata_big/path/USER_B/USER_B_TEMP.DBF' SIZE 126M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 125M;

ALTER DATABASE TEMPFILE '/oradata_big/path/USER_B/USER_B_TEMP.DBF'
AUTOEXTEND ON MAXSIZE 3000M;


reating user B:


-- USER SQL
CREATE USER USER_B IDENTIFIED BY USER_B
DEFAULT TABLESPACE "USER_B_PERM"
TEMPORARY TABLESPACE "USER_B_TEMP";

-- ROLES
GRANT "RESOURCE" TO USER_B ;
GRANT "CONNECT" TO USER_B ;
ALTER USER USER_B DEFAULT ROLE "RESOURCE","CONNECT";

-- SYSTEM PRIVILEGES
GRANT CREATE ANY VIEW TO USER_B ;
REVOKE UNLIMITED TABLESPACE FROM USER_B ;
alter user USER_B quota unlimited on USER_B_PERM;

exporting the old data
exp file=exp_user_a.exp owner=USER_A log=exp.log consistent=Y ROWS=Y


importing to new user
imp file=exp_user_a.exp fromuser=USER_A touser=USER_B log=imp.log ROWS=Y



Since I revoked the privilege UNLIMITED TABLESPACE imp creates the tables in the original tablespaces (q,w,e) but importing the data is unsuccessful:
ORA-01536: Speicherplatz-Zuteilung fur Tablespace 'q' uberschritten.

Any ideas? I do not see what is different to the docs:
http://docs.oracle.com/cd/B19306_01/server.102/b14215/exp_imp.htm#i1023312


We are using oracle 11.2.0.1 and we still have some long raw columns if that matters.

Thanks in advance,
Andreas