users in the wrong tablespace [message #71260] |
Thu, 03 October 2002 03:39 |
Teresa
Messages: 36 Registered: May 2002
|
Member |
|
|
I just discovered that I have 2 users with a permanent tablespace index1 and index3, how can I move them to a tablespace of their own, I will create a tablespace for them then how to I do a import to the contents for those tablespaces ....
Thanks
Teresa
|
|
|
Re: users in the wrong tablespace [message #71267 is a reply to message #71260] |
Fri, 04 October 2002 10:47 |
Grant
Messages: 578 Registered: January 2002
|
Senior Member |
|
|
Do a user level export then drop the objects then set their table space default to the new one and import.
1. exp system/manager file=schema_exp.dmp log=schema_exp.log owner=schema
2. verify export was successful by reviewing the log
3. drop all user objects. I drop objects and not drop user because if the user has a bunch of grants they will be retained. Your choice. Here is a good script to drop user objects: http://www.oriolecorp.com called drop_user_objects.sql.
4. Modify user to have new default tablespace and quota.
5. imp system/manager file=schema_exp.dmp log=schema_imp.log fromuser=schema touser=schema
6. Verify all objects were created, analyze objects if needed, compile invalid objects.
Good luck
|
|
|