Re: RELOCATE TABLES FROM ONE TABLESPACE TO ANOTHER ONE ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 01 Jun 1999 00:31:40 GMT
Message-ID: <375b297d.13720529_at_newshost.us.oracle.com>


A copy of this was sent to sweh_at_mpn.com (Stephen Harris) (if that email address didn't require changing) On 31 May 1999 20:05:02 GMT, you wrote:

>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 :-)
>

well, you could look at the UNREDCOVERABLE option of the CTAS command or the /*+ APPEND */ hint in an insert into statement.

sqlplus copy command is an alternative as well.

export followed by import as well.

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

any of the above will make it so you have to re-create indexes. you can rebuild indexes in 8.1 after moving a table instead of recreating them.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Jun 01 1999 - 02:31:40 CEST

Original text of this message