Alter table to a new tablespace

[BMorley]

Hi there,

Does anybody know of an easy way to change the tablespace in which a table is stored?

Is there an ALTER TABLE option for this??

Answers on a postcard please?

Thx

Ben

[sreddy]

Couple of options depending upon the version.

8i : ALTER TABLE MOVE
lower versions:
duplicate table with SELECT AS
CREATE TABLE sample_emp AS SELECT empno, deptno FROM emp ;
RENAME to TEMPORAY table in xyz tablespace and drop original table and rename it back to original table which is in xyz tablespace.

[anandharaj]

sdfgdfg

[yajman]

Hi,
There is one possible method to move the table from one tablespace to another tablespace

In Oracle 8i , ALTER TABLE EMP MOVE TABLESPACE <new tablespace>;

[akkerend]

If you use CREATE AS SELECT and RENAME you lose the constraints etc. You have to recreate them.