Re: Drop datafile from rollback tablespace without shutdown DB.

Good point, Al. Much better, of course, to do it in the order you suggest (create new ones then drop old ones) than the kacky old order that my fingers came up with when typing without thinking!

(Actually, that happens a lot whilst DBA-ing!!).

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



"Big Al" <db-guru_at_att.net> wrote in message
news:39BD4466.4B83895B_at_att.net...


> >

> > "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