Re: How do you change a tables tablespace?

I checked the Oracle docs on this, Howard. The cluster implementation you describe might be right, but a table is still a table.
Frankly speeking as a DBA, I want to manage a clustered table just like a normal table.
Partitions are implemented as segments too, yet these I can manage in an normal fashion!
Further, if it's so difficult for Oracle to find the table rows, how can it query correctly then?
Also, from a DBA standpoint, everyting in the docs tells me that a table in a cluster is
just a table sitting in a cluster! There are ** NOWHERE ** distictions being made!

Regards,
Falco

Howard J. Rogers <howardjr_at_www.com> schreef in berichtnieuws 3a68334f_at_news.iprimus.com.au...
>
> "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 Sat Jan 20 2001 - 08:09:51 CST