ora-1659 error,urgent!

[mkey]

hi all,
i created a user lets call user_b and i have a dump file from user_a. this i how i exported the user.

exp user_a/user_a@hotstring file = c:\user_a.dmp


Now i have created a tablespace for the user_b approximately 2.5 giges. The problem is when I do an import i'm getting this 1659 error. which i guess that oracle trying to use the user_a's tablespace. how can i fix this error.
Thank you so much!

 

[carp]

What is the imp command you are using to import? Are you including FROMUSER and TOUSER?

 

[mkey]

my import command is:

imp user_b/user_b@hoststring c:\user_a.dmp full = y

 

[mkey]

my import command is:

imp user_b/user_b@hoststring file = c:\user_a.dmp full = y

 

[karluk]

I think the way Oracle handles import is to put the tables in user b's default tablespace unless user b has a quota to create object's in user a's tablespace. If so, then the tables are imported to their original tablespace.

You should be able to force Oracle to put the objects in user b's default tablespace by making sure that user b doesn't have the following

1. any quota on user a's tablespace
2. the DBA role
3. the RESOURCE role

 

[karluk]

Add to the list

4. the UNLIMITED TABLESPACE system privilege

 

[mkey]

hi,
i will try what karluk suggested. but my question how should i create my dump export file so that the user_b doesn't need the dba privileges.

thanks you all for your guidance.

 

[mkey]

sorry guys,
karluk i'm not sure of your step one though. what do you mean my any quota? anyways i have added all the other priviledges that you mentioned. but i'm still getting the same ora-1659 error.

this is so frustrating.

 

[karluk]

Actually I was trying to say that user b shouldn't have any of those privileges. If those privileges have been granted, they need to be revoked. If necessary, you can restore the grants after the import.

The idea is to clamp down on user b's permissions so that he has no authority to do anything in user a's tablespace.

If user b has these privileges, they can be revoked with the following commands. (If user b doesn't have any of these permissions, then I may be misdiagnosing the situation.)

alter user user_b quota 0 k on user_a_ts;
revoke dba from user_b;
revoke resource from user_b;
revoke unlimited tablespace from user_b;

However, your point about dba privileges is well taken. As I recall only an id with dba privileges can import from an export done by a dba. I would try doing the export as user b. You may need to grant exp_full_database to user b in order to do this.