Re: How to drop a datafile from tablespace?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 19 Aug 2004 21:01:02 +1000
Message-ID: <41248889$0$9811$afc38c87@news.optusnet.com.au>

pier wrote:

> cnwy_at_263.net (yoke wang) wrote in message
> news:<42c18ceb.0408182154.5cf02d58_at_posting.google.com>...

>> I have added datafiles to a tablespace by mistake.Who can tell me how to
>> drop them?
>>
>> Thanks.
>>
>> Yoke.

>
> alter tablespace name tbs datafile 'datafile name' offline drop;

Oh dear.

To the original poster: please pay no attention to this appalling bit of advice that doesn't do what you actually asked for, but threatens to royally screw things up long-term instead.

You can NOT remove a datafile from a tablespace once added. Period. End of story.

All you can do is resize the file down to a very small size so it actually contains nothing and doesn't bother you too much. Or you can create a brand new tablespace and move everything out of the original into the new one, and then drop the entire original.

Offline dropping a file has a use in certain recovery situations. It is not something you do, or should ever want to do, to a database long-term.

HJR Received on Thu Aug 19 2004 - 06:01:02 CDT