Re: Drop datafile from rollback tablespace without shutdown DB.

"Chuan Zhang" <chuan_at_asiaonline.net> wrote in message news:2RAs5.5$h95.486513_at_news.interact.net.au...
> Dear all,
>
> Could you show me how to drop datafile from rollback tablespace without
> shutdown the DB?
>
> I got two datafiles for my rollback tablespace due to some reaseons. One
 of
> them is very small. I want to get rid of the small one. As I know, at
 least,
> I should take offline all the related rollback segments to that datafile.
> but I don't know what to do next. I could not find any "alter tablespace
 ..
> drop datafile"(I image) in Oracle Doc.
>
> Your advice is much great appreciated.
>
> Thanks in advance,
>
> Chuan

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
>
>
Received on Wed Sep 06 2000 - 06:39:58 CDT