I have User_a, default tablespace is Users_data_tbs_a(data) and Users_idx_tbs_a for indexes. And all the default indexes created by Primary key and Unique key constraints are placed in Users_idx_tbs.
And have User_b, default tablespace Users_data_tbs_b(data). and Users_idx_tbs_b for indexes. I performed following tasks to put user_a schema into user_b.
1. created dump file using user wise export
2. Revoked unlimited tablespace from user_b.
3. alter user user_b identified by *****
quota 0 on users_data_tbs_a,
quota 0 on users_idx_tbs_a,
quota unlimited on users_data_tbs_b,
quota unlimited on users_idx_tbs_b;
When try to import using
imp user_b file=userA.dmp fromuser=usera touser=userb log=userbimp.log
I observed, it is still trying to create default indexes of primary key and unique key constraints in the users_idx_tbs_a tablespace, and I am getting object already exists errors, so the result is tables are imported without primary/unique key constraints.
How to resolve this problem? Please guide me, where I am doing wrong?
My environment is SunOS, Oracle 8.1.6
Thanks In Advance.
You can not define different default tablespace for indexes. The indexes will be placed in user's default tablespace.
You will have to rebuild the indexes to the another tablesapce.
ALTER INDEX index_name REBUILD TABLESPACE tablespace_name
[Edited by SANJAY_G on 06-06-2002 at 01:00 AM]
The reason which I think is that index defination has user a's tablespace and while exporting it stores that defination only.Thats why when u import it tries to create in the same tablespace defined in its defination.Correct me if I am wrong.
I could not get why you Revoked unlimited tablespace from user_b ?
Thanks for your prompt replies.
Basically UNLIMITED TABLESPACE System Privilege To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, explicit quotas again take effect. You can grant this privilege only to users, not to roles. So, if you do not revoke this privilege from the user, he enjoy the space in another tablespace. In my case users_data_tbs_a, that is why I revoked this privilege from User_a. I hope I explained properly.
Got your point.
Click Here to Expand Forum to Full Width