Re: moving objects between tablespaces

Godwin,

You can move the table online adding "ONLINE" possibly with certain limitations on table type and you Oracle version. Otherwise, the table will not be available for any DML during move operation.

In both cases, the indexes on this table will get invalidated as indexes reference records by their rowid's which are changed as table is moved. Exception - secondary indexes on IOT tables are rows are referenced by primary key that doesn't change.

So unless your table is IOT, you will need to rebuild indexes - "ALTER INDEX index_name REBUILD".

Note that if you table is partitioned you won't be able to move the whole table with ALTER TABLE MOVE command and instead have to move each partition.

Cheers,
Alex

On 3/22/07, Godwin vincent <godwin.ror_at_gmail.com> wrote:
> Hi all,
> I want to move a table A using tablespace TB1 onto tablespace
> TB2. I can issue " Alter table A move tablespace TB2" to get this thing
> done, but my concern is,
>
> 1. If the table A has any indexes, does this moving affect them in anyway?
> do i have to move them too?
> 2. Can i perform this moving on fly or do i have to restrict anything before
> moving the table?
>
>
> Any information will be of a great help.
>
> Thanks,
> Godwin.

-- 
Best regards,
Alex Gorbachev

http://www.oracloid.com
--
http://www.freelists.org/webpage/oracle-l