Re: Reorganizing the DB.. the tricky way

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:fqa19vciv5sh6v745alr45rnnaa9ddnvaa_at_4ax.com...
> DA Morgan <damorgan_at_exxesolutions.com> wrote:
>
>
> >Create brand new tablespaces and make them LMT with uniform extent sized
> >to handle the tables that will be moved to them. Then move tables on a
> >selective basis over a period of time.
>
> That is a fine advise, but I am afraid I will have to move things back
> again - because of the tablespace name.
>
> Two things I am concerned about are: if I use "alter table <tab-name>
> online", I suppose that the table is still available while being
> moved, but what is the price I have to pay? I mean, what is the
> disadvantage of using the "online" option? I would rather do it in a
> way that I can regain space of deleted records. If using the "online"
> option prevents this, I prefer the "offline" method.

Hi Rick,

Note that you can *not* move tables online (not with the alter table command anyway). The only exception to this and why the syntax is there is to cater for Index Organised tables which are effectively "rebuilt" with the alter table online command.

>
> And 2: How can I avoid queries to fail due to indexes being in the
> UNUSUABLE state until the table is completely moved and the index is
> rebuilt?
>

Generally, queries won't fail, they'll just run like a three legged dog.

> Put together: How can I reorganize the DB without affecting its
> availability?
>

Only reorg if necessary. Some of the reasons you provide sound a little questionable.

If availability is a vitally important consideration and a reorg is a must, then check out the dbms_redefinition package which does allow you to move and reorg tables without impacting availability. It's a bit cumbersome but it might meet your needs.

Cheers

Richard

> Bye
> Rick Denoire
Received on Mon Apr 07 2003 - 07:56:53 CDT