Re: Drop datafile from rollback tablespace without shutdown DB.

>
> "Chuan Zhang" <chuan_at_asiaonline.net> wrote
> > Dear all,
> >
> > Could you show me how to drop datafile from rollback tablespace without
> > shutdown the DB?
> ><<snipped>>

"Howard J. Rogers" wrote:
> Hi Chuan,
>
> Once a datafile has been added to a tablespace, you're stuck with it for
> life (short of dropping the entire tablespace and recreating it from
> scratch).
>
> I would have suggested 'alter database datafile 'XX' resize 10K' but you say
> the thing is already very small, so that won't help you -the point being
> that although you can't drop a datafile from a tablespace, you can make it
> insignificantly small. But it seems someone already has thought of that!
>
> The only other alternative is to drop the entire tablespace 'including
> contents', remove the datafiles at the OS level, then recreate the thing and
> then recreate the relevant rollback segments. You'll have to comment out
> the rollback_segments line in the init.ora file first, of course.
>
> You can only drop rollback tablespace if all the segments are already
> offline. And you can only take a rollback segment offline if it isn't being
> used.
>
> Regards
> HJR
We have resized our rollback tablespaces many times as the system has grown from 150GB to 1TB. We do it the way Howard says with one addition. We create the new tablespace and rollback segments first, bring them online, and then drop the old tablespace and rollback segments. We are about to grow from 2 tablespaces with 10 large rollback segments each to 3 tablespaces. It's pretty easy using scripts. Don't forget to change the init.ora parms for the new names.

Big Al Received on Mon Sep 11 2000 - 15:47:06 CDT