Re: How do you change a tables tablespace?

"Falco Paul" <falco_at_palm.nl> wrote in message news:9498uo$i98$1_at_azure.nl.gxn.net...
> 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

Utter rubbish. A table that is stored within a cluster doesn't physically exist. That's one good reason why the move command won't work on it. The *cluster* is the segment: the tables within it are merely logical entities. If you examined the blocks of a cluster, you would see physically denormalised data, comprising data from two (or more) logical tables.

Trying a "move" command on a cluster table would require the command to work out which bits of a block relate to one table, and which to another. Forget it! Clusters are just too physically complicated for this to be even considered.

Regards
HJR
>
> "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 - 06:30:29 CST