Exp/imp

[mike73]

I am trying to import tables from user A to another user B on the same database. Both the users have different default tablespaces. But when I try to import into user B , it puts all the tables in user A's tablespace . I want the tables to be imported in user B's tablespace.
Here what I did but I still get problem

1. REVOKE UNLIMITED TABLESPACE FROM UBER_B
2. ALTER USER USERNAME QUOTA 0 ON TABLESPACE_B

I don't know what I did wrong but it's not wokring. The question I have is on item 2 USERNAME, is it USER_A or USER_B. The second question I have is TABLESPACE, is it tablespace in tablespace_A or tablespace_B ???

What is solutions and please give me a detial step of how to make this work.

Thanks

 

[jcote]

Check to make sure that user B doesn't have the "resource" privilege. I am responding without actually checking first, but I know that by granting resource, a user will be allowed to create objects in every tablespace. So the revoke you did might have had no impact beacause you have resource.

 

[kindus]

May be User is having DBA role, you may forget all those. Here it is how you can achieve what u want.

Login as user_b
1) create table <yourtable> tablespace tablespace_b as select * from user_a.<yourtable> where 1=2
2) import ....... ignore=Y

Specify other import parameters which are appropriate where IGNORE=Y is a must.

hope it helps

 

[lweng]

If you put your imp script here we might get better understanding. Did you use fromuser=A in the script?

 

[rcurva]

I think, you have to do this instead:

1. REVOKE UNLIMITED TABLESPACE FROM UBER_B;

2. Give unlimited tsp quota to User_B for TABLESPACE_B and TEMP (or whatever your temp tsp is)
ALTER USER USER_B QUOTA UNLIMITED ON TABLESPACE_B;
ALTER USER USER_B QUOTA UNLIMITED ON TEMP;

Assuming TABLESPACE_b is the default tablespace of USER_B, and TEMP is the temporary tablespace used by USER_B.