Re: alter table move

Well, in my 8.1.7.3 (AIX) I can do
alter table http_status_detail move tablespace stsd nologging;

I can't tell you the exact impact of NOLOGGING in this particular case if the instance crashes while this operation is going on but it works fast enough for me when the instance doesn't crash :-)

regards,
Andre van Winssen

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:aa3284$189$1_at_lust.ihug.co.nz...
> 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 Wed Apr 24 2002 - 04:31:19 CDT