Re: Move Table to another tablespace?

Hi Terry,

OK, I may not have been clear the first time so I'll try again ;)

The command to move a heap table is:

alter table uwi_mjf
move tablespace atcorwork;

However, your command was:

alter table uwi_mjf
move online tablespace atcorwork;

Note the difference, the keyword *online*.

Now you can not move a heap table online, well not with this syntax anyway (see dbms_redefinition). So what does the online signify ?

Well if you wish to reorganize an Index Organized Table (or in other words, rebuild), then this is also the syntax to do this. Note you do not 'rebuild' an IOT, you move it. But because it's an index (in a table sort of way) you can also 'rebuild' an IOT online, just like you can rebuild a B-Tree index online. And the syntax to rebuild an *IOT* is as you described.

However, you can not rebuild indexes online with SE, you need EE and that is why you got the error message:

ERROR at line 1:
ORA-00439: feature not enabled: Online Index Build

Now I've forgotten why we were discussing all this but I hope all is now clear ;)

Cheers

Richard

"Terry Dykstra" <dont_reply_tddykstra_at_forestoil.ca> wrote in message news:_FF1a.104785$c41.4067534_at_news2.telusplanet.net...
> I'm not following. Isn't this the command to move a table to a different
> tablespace?
> In my example, table uwi_mjf is a simple table with no indexes or
> primary/unique constraints.
>
> --
> Terry Dykstra
> Canadian Forest Oil Ltd.
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:O_r1a.44012$jM5.110996_at_newsfeeds.bigpond.com...
> > "Terry Dykstra" <dontreply_tddykstra_at_forestoil.ca> wrote in message
> > news:rFQ0a.127207$Ui4.4196165_at_news1.telusplanet.net...
> > > You need Enterprise Edition. Using 817 SE you will get a nice error:
> > >
> > > SQL> alter table uwi_mjf
> > > 2 move online tablespace atcorwork;
> > > alter table uwi_mjf
> > > *
> > > ERROR at line 1:
> > > ORA-00439: feature not enabled: Online Index Build
> > >
> > > --
> >
> > Hi Terry,
> >
> > The OP and Sybrand were talking in relation to moving a *table* to
another
> > tablespace.
> >
> > Your example nicely shows you can not recreate an *index* online,
without
> > EE, which is hardly the same thing. Admittedly an index organised table
is
> > close but it's the index online feature that is being violated here.
> >
> > Cheers
> >
> > Richard
> >
> >
> >
> >
>
>
Received on Mon Feb 10 2003 - 05:08:03 CST