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

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 09 Nov 2006 11:00:58 -0800
Message-ID: <1163098858.658566@bubbleator.drizzle.com>

Ben wrote:

> EscVector wrote:
>> Ben 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.

>> Just an FYI: if destination tablespace/datafile is on autoextend and it
>> extends out, it will not shrink back automatically if the move fails.
>> This could happen if the physical space runs out and you are using
>> autoextend.
> 
> 
> On that line of thought.
> I'm under the impression that when building ( or rebuilding ) an index
> it won't trigger an autoextend on the datafile and if you run out of
> room on the file, the build will fail with an "out of space" error.
> I've seen this happen but haven't ever seen it documented, is that true?

I don't know whether that is true.

Consider using a resumable transaction.

You can find a demo in Morgan's Library at www.psoug.org under DBMS_RESUMABLE.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Nov 09 2006 - 13:00:58 CST