Re: Reorganizing the DB.. the tricky way

Rick Denoire wrote:

> 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.
>
> 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?
>
> Put together: How can I reorganize the DB without affecting its
> availability?
>
> Bye
> Rick Denoire

What is required here requires either taking things off-line and doing the dirty work on Christmas Eve (or similar) or doing it meticulously over a period of time.

The end result should be that the old tablespaces, and their associated datafiles, are dropped. That is your space recovery.

Daniel Morgan Received on Sun Apr 06 2003 - 18:01:34 CDT