Re: Move user to different tablespace

"Terry Dykstra" <dontreply_tddykstra_at_forestoil.ca> wrote in message news:<x4hE8.16159$27.582584_at_news2.telusplanet.net>...
> 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...
> > 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.

Linda and Terry, the "alter table owner.table_name tablespace x;" command is also available in 8.1. With version 9 the 'online' option becomes available allowing update DML to continue to run while the rebuild is in process. In 8i no updates are allowed during the relocation/rebuild.

This command saves the grants, constraints, and synonyms on the table.  The indexes need to be rebuilt after the table is moved to update the target row ids.

HTH -- Mark D Powell -- Received on Wed May 15 2002 - 08:16:18 CDT