Re: RELOCATE TABLES FROM ONE TABLESPACE TO ANOTHER ONE ?

Thomas Kyte (tkyte_at_us.oracle.com) wrote:

: SQL> alter table emp move tablespace some_other_tablespace;

Neat :-)

: is implemented -- no. You would typically export the table. drop it and
: recreate it in the 'right' tablespace and import it.

Co-incidently I was having a similar discussion with co-DBA here, and the best we could come with was:
  rename x to y
  create table x tablespace z as select * from y   drop table y

Of course, you'd have to check your storage constraints were OK on the new table. Space _shouldn't_ be a problem. Expect lots of archive logs :-)

Would this affect/break any indexes? Or would they be rebuilt?

-- 

rgds
Stephen