alter table move... What Oracle version?
Syltrem
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)
Sybrand Bakker
Hth,
Sybrand Bakker, Oracle DBA
"Syltrem" <syl...@videotron.ca> wrote in message
news:Wmo96.7224$hP5.2...@wagner.videotron.net...
Falco Paul
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" <pos...@sybrandb.demon.nl> schreef in bericht
news:t6c6pdd...@beta-news.demon.nl...
Brian Peasland
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
--
========================================
Brian Peasland
Raytheons Systems at
USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my
company!
========================================
robertg...@my-deja.com
Robert
In article <946lqh$m20$1...@azure.nl.gxn.net>,
Sent via Deja.com
http://www.deja.com/
Falco Paul
Falco
<robertg...@my-deja.com> schreef in bericht
news:946v5l$5lj$1...@nnrp1.deja.com...
Falco Paul
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" <peas...@edcmail.cr.usgs.gov> schreef in bericht
news:3A66FB02...@edcmail.cr.usgs.gov...
Brian Peasland
partition you want moved. But you said that partitioning is not even an
installed option. This might be a bug on your part. I was able to move
tables in Oracle 8.1.6 and 8.1.7 on NT platforms.
HTh,
Brian
robertg...@my-deja.com
partitions.
Robert
In article <9472d9$pl9$1...@azure.nl.gxn.net>,
Falco Paul
Even in different installations on different systems!!
Weird, huh!
Falco
<robertg...@my-deja.com> schreef in bericht
news:9477mr$e8k$1...@nnrp1.deja.com...
Falco Paul
Even in different installations on different systems!!
Weird, huh!
Falco
"Brian Peasland" <peas...@edcmail.cr.usgs.gov> schreef in bericht
news:3A67223E...@edcmail.cr.usgs.gov...
Falco Paul
This would pherhaps explain the difference!
Falco
"Falco Paul" <fa...@palm.nl> schreef in bericht
news:9497lj$hv1$1...@azure.nl.gxn.net...
Falco Paul
However, note that you cannot move a table that's in a cluster!
Regards,
Falco
<robertg...@my-deja.com> schreef in bericht
news:9477mr$e8k$1...@nnrp1.deja.com...
Falco Paul
However, note that you cannot move a table that's in a cluster!
Falco
"Brian Peasland" <peas...@edcmail.cr.usgs.gov> schreef in bericht
news:3A67223E...@edcmail.cr.usgs.gov...
Falco Paul
However, note that you cannot move a table that's in a cluster!
Regards,
Falco
"Brian Peasland" <peas...@edcmail.cr.usgs.gov> schreef in bericht
news:3A66FB02...@edcmail.cr.usgs.gov...
Howard J. Rogers
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" <fa...@palm.nl> wrote in message
news:9498oh$i8m$1...@azure.nl.gxn.net...
Howard J. Rogers
newbies. I really wish people would *test* this stuff before posting. And
test it on boring old EMP, which at least has the advantage of being a
perfectly normal table accessible to all.
To post this and *then* discover that your test table actually doesn't exist
at all except as some logical construct within a cluster is simply to
confuse people who don't deserve to be.
If you (or anyone else) purports to proffer solutions, one might reasonably
expect the "solutions" to have gone through the minimum of testing.
What makes this case particularly egregious is that the Oracle documentation
on the subject is utterly un-ambiguous, and all and any Oracle Press books
would have given the correct answer in a matter of minutes.
HJR
"Falco Paul" <fa...@palm.nl> wrote in message
news:9471n8$peb$1...@azure.nl.gxn.net...
Martin Haltmayer
Martin
Syltrem
Thanks for the answer (and all the clarification that followed), I know I
will have to go to 8.1.x. In the meantime I will EXP-IMP. Still.
Regards,
--
Syltrem
http://pages.infinit.net/syltrem (OpenVMS Web Site)
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:t6c6pdd...@beta-news.demon.nl...
Falco Paul
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.
Tables in a database should be a logical unit that you can work with.
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!
If we follow your argument, than one could easily argue that tables don't
exist in a tablespace either!
FP
Howard J. Rogers <howa...@www.com> schreef in berichtnieuws
3a68...@news.iprimus.com.au...
Howard J. Rogers
"Falco Paul" <fp...@wanadoo.nl> wrote in message
news:3a699391$0$29333@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