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

DA Morgan wrote:
> 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

I'll have to try to reproduce it and then post it, as I'm fairly certain that is why it died. Received on Thu Nov 09 2006 - 13:42:34 CST