Re: alter table move... What Oracle version?

"Falco Paul" <fpaul_at_wanadoo.nl> wrote in message news:3a699391$0$29333_at_reader5...
> Excuse me!?!
>
> Anyway, I would like to *move* the *table* even though it doesn't *exist*
 in
> the bloody *cluster*!
>
> And by the way:
> I think you are totally wrong. It's just how you look at things.
>

I see. You post something which isn't true, and *I'm* the one that's wrong. Yup, makes sense to me.

> Tables in a database should be a logical unit that you can work with.
>

I like the cunning use of the subjunctive here. "Should" doesn't mean "are".

> Whether the table is stored in a cluster, a tablespace, an index or
 whatever
> else we can come up with, shouldn't give a damn
> as to how I can manage the table. It's just rows and columns stored
> somewhere, for Gods sake!

That's precisely the point: It's not. A cluster's blocks store rows and columns from *two or more* tables. Physically, the block contains information from two entities, constructs, objects ...call them what you will. But there is a *physical* difference between a cluster and a table, and it's that physical difference that makes what you posted so daft. To move a table in a cluster would require Oracle to un-pick the scrambled mess that is within the blocks of the cluster, and to separate out one physical part from another. It could probably be done -it's only programming after all- but in the process of moving the data from one table out of the cluster, what's left behind *in* the cluster is no longer functional. For no other segment type does a move request imply a command to break the original segment.

In short, asking to move tables within a cluster would constitute a much bigger thing than a simple move: you'd be asking to "de-Cluster" your cluster. There are techniques for doing that already (create table newblah as select * from blah would do it). But quite reasonably, I think, Oracle is suggesting that you should have to explicitly stuff up your Cluster, not have it done implicitly by what appears to be an innocent move request.

>
> If we follow your argument, than one could easily argue that tables don't
> exist in a tablespace either!
>

Only if you wilfully misrepresent or misunderstand my argument. My argument is simply that clusters contain rows from multiple tables. Tables contain rows from one table. Makes all the difference in the world.

HJR
> FP
>
> Howard J. Rogers <howardjr_at_www.com> schreef in berichtnieuws
> 3a68385b_at_news.iprimus.com.au...
> > Don't be bloody stupid! Tables that are in a cluster don't even
 *exist*,
 so
> > how the hell you propose to move them, I haven't a clue.
> >
> > When you have a cluster, the *cluster* is the segment, and *it* alone
> > exists, physically. The tables within the cluster may be referenced as
> > *logical* entities, but that's your lot.
> >
> > HJR
> >
> >
> > "Falco Paul" <falco_at_palm.nl> wrote in message
> > news:9498oh$i8m$1_at_azure.nl.gxn.net...
> > > Got it working OK now.
> > > However, note that you cannot move a table that's in a cluster!
> > > Regards,
> > > Falco
> > >
> > >
> > > <robertgfreeman_at_my-deja.com> schreef in bericht
> > > news:9477mr$e8k$1_at_nnrp1.deja.com...
> > > > Is this a partitioned table?? If so, you must move the individual
> > > > partitions.
> > > >
> > > > Robert
> > > >
> > > > In article <9472d9$pl9$1_at_azure.nl.gxn.net>,
> > > > "Falco Paul" <falco_at_palm.nl> wrote:
> > > > > Weird,
> > > > >
> > > > > Why do I get this?
> > > > >
> > > > > SQL> alter table test move tablespace pbs_data;
> > > > > alter table test move tablespace pbs_data
> > > > > *
> > > > > Error in line 1:
> > > > > ORA-14004: missing PARTITION keyword
> > > > >
> > > > > Even though partitioning in not installed?
> > > > > I tried all kind of combinations, but no result!
> > > > > Is this some bug? Which version are you using?
> > > > > I am running 8.1.6 AND 8.1.7
> > > > > Both have the same problem!
> > > > >
> > > > > Regards,
> > > > > Falco
> > > > >
> > > > > "Brian Peasland" <peasland_at_edcmail.cr.usgs.gov> schreef in bericht
> > > > > news:3A66FB02.C9E5E5D4_at_edcmail.cr.usgs.gov...
> > > > > > This is not only for IOT's. See below:
> > > > > >
> > > > > > SQL> CREATE TABLE test_move (
> > > > > > 2 id NUMBER,
> > > > > > 3 name VARCHAR2(20))
> > > > > > 4 TABLESPACE users;
> > > > > >
> > > > > > Table created.
> > > > > >
> > > > > > SQL> select table_name,tablespace_name from user_tables
> > > > > > 2 where table_name ='TEST_MOVE';
> > > > > >
> > > > > > TABLE_NAME TABLESPACE_NAME
> > > > > > -------------------- ------------------------------
> > > > > > TEST_MOVE USERS
> > > > > >
> > > > > > SQL> alter table test_move move tablespace tools;
> > > > > >
> > > > > > Table altered.
> > > > > >
> > > > > > SQL> select table_name,tablespace_name from user_tables
> > > > > > 2 where table_name ='TEST_MOVE';
> > > > > >
> > > > > > TABLE_NAME TABLESPACE_NAME
> > > > > > -------------------- ------------------------------
> > > > > > TEST_MOVE TOOLS
> > > > > >
> > > > > > See, the table moved!
> > > > > >
> > > > > > HTH,
> > > > > > Brian
> > > > > >
> > > > > >
> > > > > > Falco Paul wrote:
> > > > > > >
> > > > > > > I believe this only works for IOT's.
> > > > > > > Heap tables don't move at all with this command (at least not
 in
 8.1.6,
> > > > > > > didn't test 8.1.7 yet).
> > > > > > > I heard Oracle 9 should support a 'true'ALTER TABLE REBUILD
 (storage
 specs)
> > > > > > > command.
> > > > > > >
> > > > > > > Falco
> > > > > > >
> > > > > > > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> schreef in
 bericht
> > > > > > > news:t6c6pddh43757f_at_beta-news.demon.nl...
> > > > > > > > 8.1 aka 8i only.
> > > > > > > >
> > > > > > > > Hth,
> > > > > > > >
> > > > > > > > Sybrand Bakker, Oracle DBA
> > > > > > > >
> > > > > > > > "Syltrem" <syltrem_at_videotron.ca> wrote in message
> > > > > > > > news:Wmo96.7224$hP5.247921_at_wagner.videotron.net...
> > > > > > > > > I think there is a new command to move tables between
 tablespaces in
 Oracle
> > > > > > > > > 8, but is it available on 805 or only starting with 810?
> > > > > > > > >
> > > > > > > > > And would this be the correct syntax?
> > > > > > > > > ALTER TABLE XX MOVE TABLESPACE ZZ STORAGE (INITIAL nn);
> > > > > > > > >
> > > > > > > > > Thanks!
> > > > > > > > > --
> > > > > > > > > Syltrem
> > > > > > > > > http://pages.infinit.net/syltrem (OpenVMS Web Site)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > > ========================================
> > > > > > Brian Peasland
> > > > > > Raytheons Systems at
> > > > > > USGS EROS Data Center
> > > > > > These opinions are my own and do not
> > > > > > necessarily reflect the opinions of my
> > > > > > company!
> > > > > > ========================================
> > > > >
> > > > >
> > > >
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > >
> > >
> >
> >
>
>
Received on Sat Jan 20 2001 - 15:40:00 CST