Re: alter table X move tablespace Y. what happens if error?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 9 Nov 2006 07:18:03 -0800
Message-ID: <1163085483.517970.72620@k70g2000cwa.googlegroups.com>

On Nov 9, 10:08 am, "Ben" <bal..._at_comcast.net> wrote:
> 9.2.0.5 EE AIX5L
>
> I've searched on AskTom, looked at the Admin & Concepts manuals, and
> searched here but I can't find anywhere that tells me what happens to
> my table if an 'ALTER TABLE X MOVE' fails.
>
> I thought I remembered reading somewhere that the table move creates
> temp segments in the new tablespace that are converted to permanent
> segments after completion. So I can see that the new segment would
> cease to exist if it failed during execution. But what happens to the
> original? Nothing I'm assuming, but I just wanted to revist this before
> I actually perform.

If an alter table move statements fails, say due to lack of free space, then the original table is still there unchanged and usable.

On the other hand if the table moves successfully then all indexes including the PK are invalid and require rebuilding.

HTH -- Mark D Powell -- Received on Thu Nov 09 2006 - 09:18:03 CST