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?
Couple of options depending upon the version.
8i : ALTER TABLE MOVE
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.
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>;
If you use CREATE AS SELECT and RENAME you lose the constraints etc. You have to recreate them.
Click Here to Expand Forum to Full Width