Re: alter table move

You originally asked for, and I quote, "Any sugested additional options/parameters?". Strangely enough, parameters and options are one of the few things absolutely guaranteed to change between versions. Why you would think version information optional therefore beats me. Why you think someone asking for it is "strict", beats me. Why you think that "support" needs different information from "advice" beats me: after all, support is only the offering of advice to achieve an outcome, whereas advice is the offering of advice with no particular outcome in mind. Both still involve offering advice (and if support is any good, it'll be the same advice).

So one's Oracle version is not optional, and if we say it often enough, perhaps posters here might eventually get the message.

As for your original post, I can't really work out what you're after. I have experience with moving mult-million row tables online. It's not particularly pleasant, but DML on the table at least is possible during the rebuild. There are, of course, massive amounts of disk I/O taking place, and you can expect the I/O induced to partially cripple work done elsewhere on the database (one grudging reason why indexes might need their own tablespace/hard disk drive, Daniel!). You do need to have room to build the IOT that holds the DML that will be played against the index to bring it up to date. You also need to have room to have two versions of the index concurrently (albeit briefly), though that's nothing new and was a requirement for any index rebuild going back to version dot. As to additional parameters or options, there are none so far as I can think of. It just happens. Think of it as a free gift from Oracle, which doesn't happen very often (call me cynical!).

Online rebuilds require an exclusive lock momentarily on the table when they start, and when they want to finish. Therefore, table locking hasn't gone away entirely, and that might cause your Users a bit of grief. Hence Oracle's somewhat perverse advice not to online rebuild indexes on tables which are subject to heavy amounts of DML -which are probably, of course, exactly the tables that you might have thought this feature was invented for!

With higher versions of Oracle, additional types of indexes can be rebuilt online (such as function-based indexes, reverse keys and so on), but there are no fundamental changes to the basic way an online rebuild takes place, and the "no heavy DML" advice is pretty much the same.

If there are any more specific queries about this, let us know and I (and I'm sure the others) will do my best.

Regards
HJR "Krzysztof Kryczka" <kryczus_at_rpg.pl> wrote in message news:aa2vct$rvm$1_at_news.tpi.pl...
> version 8.1.6.1.0 EE
> but dont be so strict like support - i'm just looking for advices not
> solution
>
> User "Bricklen Anderson" <bricklen_at_shaw.ca> wrote
> news:3CC4A312.A8F39E9_at_shaw.ca...
> > I think you missed the part about what version of Oracle are you running
> > (rather crucial for a useful response).
> >
> > Krzysztof Kryczka wrote:
> > >
> > > ok - if you want detail you can have it:
> > > handware Dell PowerEdge6400
> > > OS is Win2000AS
> > > large for me is:
> > > sum(bytes) from dba_extents is about 1.2G
> > > sum(blocks) from dba_extents is about 7000
> > > any more details?
> > >
> > > i have about 4 tables like that and have to reorganize their storage
> online
> (....)
>
>
Received on Tue Apr 23 2002 - 02:23:45 CDT