Re: How do you change a tables tablespace?

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 Thu Jan 18 2001 - 06:25:03 CST