alter table reassign tablespace???

[mary]

I have a table with ~15 mil records which I'd like to move to a different tablespace. Is there a way to move it other than doing export/import.
Thnkx.

[jmodic]

Since 8i you can *move* table with a single DDL. The basic syntax is

ALTER TABLE your_table MOVE new_tablespace;

You can also change the storage parameters during this rebuilding. Check the whole syntax in SQL manuals.

HTH,

[mary]

thank you.
I was trying to find more information in Oracle documentation throught their web_page.
[url]http://technet.oracle.com/docs/products/oracle8i/doc_index.htm[/url]
next to the 'alter table move ...' it says GTK 3-29. Would anyone know how to find that particular section. I seem to have a little problem finding it.
Thank you.

[jmodic]

GTK = Getting to Know Oracle8i, 3/29 means chapter3/page29.

On the bottom of each page of the Generic Documentation Master Index you'll find a legend what each abrevation in index mean. Bevare, the legend is printed on each *pair* of pages (it was designed for hardprint, although it is a little inconvinient for electronic edition as you sometimes have to scroll one page up or down to find out what particular key means).

[mary]

I cannot find anything about moving a table from one tablespace to another in Oracle Doc's.
'alter table move..' seems to apply in situations when you want to move partitions.
I will keep looking. Meanwhile if someone have done this already please let me know. Thanks.

[rcaballe]

The alter move will work.
I have done it.

[marist89]

I've done it thousands of times. It works really well...

[jmodic]

No, no, moving partitions was supported allready in 8.0, while reorganizing tables with ALTER TABLE MOVE is new in 8.1. Are you sure you are looking at 8.1 (aka 8i) manuals? If yes, go to "SQL Reference" manual and find ALTER TABLE command description. In mine (it is for release 8.1.5) it is in chapter 7, page 113. More specificaly, railroad diagram for "move_table_clause" which is a part of that command is on page 116 and is described in more detail on page 133.

HTH,

[mary]

Jmodic
thank you for your support. I don't seem to find that diagram in doc's I am looking at. I am having troubles opening .pdf file so I am looking at doc's online. I still don't see page numbers there.
The syntax 'alter table move /tablespace name/ posted above doesn't work. I get ORA-'14133: ALTER TABLE MOVE cannot be combined with other operations'.
Would someone post a correct syntax here.
Jmodic, if you're looking at doc's online pleased post a link.
Appreciate everyone's help.

[marist89]

Check to make sure you know what type of table you are moving. We assumed just a normal table, but an IOT table has different considerations.
See:
[url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem2a.htm#2055687[/url]