Re: How do you change a tables tablespace?

"Falco Paul" <fpaul_at_wanadoo.nl> wrote in message news:3a699c9d$0$29329_at_reader5...
> 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
>

Then you don't understand what a cluster is. A cluster physically stores the rows from two or more tables within the one Oracle block. Partitions store the rows from just one table. Partitions are, therefore, 'single entities', even though you can conceive of them at one level as being simply *part* of a table.

There is a huge, physical, real and tangible difference in the way the data is stored in an Index cluster as opposed to any other sort of segment. *That* is the distinction to be made, and that's why a move for a table or a partition is easy to do, and for a Cluster is extremely hard to do.

I can forgive a developer not understanding this distinction, since they spend their lives doing nothing but writing code which references the logical entities in the database, and their code shouldn't give a damn whether what they are referencing is stored in SYSTEM tablespace or DATA, in 10 extents or 1000, or as a partitioned table or as a cluster.

But DBAs are the people that are *supposed* to understand what is happening at the storage and physical levels, and certainly need to understand the very real, physical differences in the *way* things are stored, and the very real issues that can arise from *where* things are stored.

As to how it can query cluster tables, and yet not move them: it's actually quite hard for it to locate the rows from any one table inside an Index cluster, and if you were querying such a cluster regularly in that fashion, you would be mad to have implemented it as a cluster in the first place. They are designed for speeding up retrieval of data from two or more tables which are *always* queried in the same, joined fashion, and always with the same joining 'where' clause. Because the rows from the two tables are physically stored together; because, in a sense, Oracle has physically pre-joined the tables, physically retrieving the joined data is much quicker than it would be with separate table segments. You start querying just *one* of the tables, and we now start having to physically wade through records from one or more tables that you suddenly are no longer interested in before we can find the few bits of information that you are interested in. Therefore, much more work for Oracle, and much slower performance, than would have been the case with separate tables.

As for the "move" bit -can't you see that to decide to move one of the tables that forms part of a cluster would not only result in one of your tables being moved, but would also result in the old cluster missing half its data? You might just want to move one table, but you end up breaking the cluster as well.

I dare say it could all be done with relative ease if Oracle put its mind to it, but the implications are much, much bigger than just moving a table or a partition of a table (moving a partition won't break anything, since partitions are designed to be stored in different places from each other anyway, for example). Accordingly, they've made the decision that, for now, it's not on -and if you actually understand what a Cluster is, that decision makes perfect sense.

HJR
>
> 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 - 15:34:28 CST