Re: How do you change a tables tablespace?

Got it working OK now.
However, note that you cannot move a table that's in a cluster! I can't think of any good reason why you shouldn't be able to move a table that's in a cluster, really.
A cluster is really not much more than a special way of storing data. Regards,
Falco

"Falco Paul" <falco_at_palm.nl> schreef in bericht news:946ngg$mhc$1_at_azure.nl.gxn.net...
> I believe that ALTER TABLE MOVE only works for IOT, not heap tables.
> Oracle 9 is supposed to include a true ALTER TABLE REBUILD storagespec
> command for heap tables.
> Regards,
> Falco
>
> "Howard J. Rogers" <howardjr_at_www.com> schreef in bericht
> news:3a643139$1_at_news.iprimus.com.au...
> > I am prepared to be proved wrong, but I practically guarantee that the
> > 'alter table move tablespace blah' command is new in 8i, not 8.
> >
> > Other than that, you sum it up succinctly.
> >
> > Perhaps you might point out that your option 1 preserves permissions,
 but
> > the Indexes on the table need to be rebuilt; Option 2 requires all
 indexes
> > and constraints to be re-created, and all permissions to be re-granted,
 and
> > Option 3 is utterly impossible using normal import commands because
 import
> > always attempts to create the new table in exactly the same tablespace
 as
> > the tablespace the segment came from -and if there is one tablespace we
 can
> > guarantee exists in all databases (and hence will be re-used on import)
 it's
> > SYSTEM. Accordingly, your Option 3 requires an import to be done in
 such
 a
> > way as to create an editable text file, without actually importing the
> > object; the text file needs to be edited to change the tablespace; the
 text
> > file needs to be run as a piece of DDL, and import then needs to be
 *re*-run
> > with ignore=y.
> >
> > Regards
> > HJR
> >
> >
> > "Myron Wintonyk" <mwintony_at_med.ualberta.ca> wrote in message
> > news:3A6404F8.C36DF515_at_med.ualberta.ca...
> > > Here are 3 possibilities:
> > >
> > > 1. Alter table move tablespace NEW; (requires Oracle 8 and no
> > > long columns)
> > >
> > > 2. create table temp as select * from TABLE;
> > > rename table TABLE as oldtemp;
> > > rename table temp as TABLE;
> > > rebuild indexes
> > > (requires no long columns).
> > >
> > > 3. export the table (exp utility)
> > > drop the original table
> > > rebuild the table (empty) in the new tablespace
> > > import the table (imp uqility).
> > >
> > > I've listed the answers from easiest to most difficult. Let us know
 if
> > > you need further details on
> > > option 3.
> > >
> > >
> > > Robert Sherunkle wrote:
> > >
> > > > Hi,
> > > >
> > > > I have some tables which have been created in the SYSTEM tablespace.
> > > > How can I move them without losing the data.
> > > >
> > > > Thanks In Anticipation
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > >
> >
> >
>
>
Received on Fri Jan 19 2001 - 05:35:04 CST