Tables move from tablespace

[Seenivas]

Hi All,

I am a new member and new into DBA.

Is it possible to move a table from one table space to another table space?

Thanks in advance

[Bore]

alter table move tablespace
works after 9i and locks the entire table for the duration of the operation.
Not sure but I think the corresponding indexes will be made unisable and must be rebuilt.
It is possible to be necessary to issue
alter table enable row movement;
before issuing alter table move

Regards

[davey23uk]

yes you do need re-build indexes, no you dont need to enable row movement

[slimdave]

alter table enable row movement;

Not relevant -- that enables row movement between partitions.

[Bore]

Dave,
I checked and really in this case row movement is not necessary, however I do not agree that it means enable movement between partitions. It rather means enable rowid change.
Row movement is for sure necessary when you do segment shring in 10g or when you do flashback table and that has nothing to deal with the partitioning. That's why, since move tablespace leads to rowid changes, I presumed that enable row movement could be necessary

Best
Boris

[davey23uk]

row movement has everything to do with partitions, if you update the partition key of a record and it needs to go to a different partition, you will need row movement turned on for that to work

[slimdave]

Row movement is for sure necessary when you do segment shring in 10g ...

Ah ha, quite so. I learned something there. Thanks Boris, now I need an excuse to go and shrink a segment ...

[Bore]

Davey23uk (many Dave's here :-) )
I have not said that row movement has nothing to deal witgh partitioning. Of course it has. What I say is that row movement is ALSO necesary for operations like shrink and flashback, and these operations are permitted and defined on non-partitioned tables as well. So, Slimdave is not wrong here, just his first statement is not fully precise.

Regards
Boris