Re: Drop datafile from rollback tablespace without shutdown DB.

In article <39BD4466.4B83895B_at_att.net>,
  db-guru_at_bigfoot.com wrote:
> >
> > "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
>

This is, by far, the best method as it maintains active rollback segments. Dropping the original segments/tablespace and then creating a new tablespace/segments results in a period of time when the users cannot use the database.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.