Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Move user to different tablespace
Yo don't create a user in a tablespace. You assign him a default tablespace
where his segments (tables, indexes) get created if he doesn't specify the
tablespace parameter when creating the segment.
To change his default tablespace use ALTER USER ABC DEFAULT TABLESPACE XYZ;
In 9i you can simply move the objects to a different tablespace.
In 8.x you can rebuild the indexes quickly using ALTER INDEX and specify a
different tablespace.
But for tables etc you have to use something like export/import. You can
get the DDL statements straight from these. Look in the Utilities guide for
the exact syntax. The freeware tool DBATOOL makes this process very easy to
do.
http://www.databee.com/main.htm
-- Terry Dykstra Canadian Forest Oil Ltd. "Linda Lee" <goforticket_at_yahoo.com> wrote in message news:f901fb80.0205141257.3b72a895_at_posting.google.com...Received on Tue May 14 2002 - 18:28:29 CDT
> Hi,
>
> I created a user in a wrong (default) tablespace, and then all the
> user's object were created in the tablespace.
>
> How can I move the user's schema from one tablespace to another
> tablespace? I tried "export/drop user/create user/import". But it
> seems to me that the user's priviledges and grants are all lost after
> doing that.
>
> Is it a better way to do that?
>
> thanks.