Re: Unable to drop a datafile
Date: Wed, 5 Sep 2012 22:43:10 -0300
Message-ID: <CAJ2dSGRwQwayY6b+0VqXTMKGvXHgvBGv8NbpAXXPCi7qRaJ+=Q_at_mail.gmail.com>
I may be wrong... but I don't think you're supposed to drop a datafile... http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:515222144417
Yes, yes, I know, this is pre-10.2, but it's still a good guideline (and a very interesting read).
Could you show us the "script" for the tablespace? (i.e. how many datafiles, how big is each DF, storage clauses, etc)
if you are sure you won't need it, you might try alter database datafile 'xxx' offline drop; (syntax may differ) but if I remember correctly this only works when you will drop the TBS afterwards... which may not be what you want.
Safest approach (unless EBS/SAP) is to move the objects to a new TBS and then drop the old one. Or use TBS reorg to release space...
btw, if you are on ASM, bigfile is your friend!
oh, oh, also, check if you are using recycle bin. You didn't mention whether you recently removed tables or not, but I'm taking a wild guess and assuming you removed some data (tables, partitions, etc) and you want the space back. If you simply *DELETED* data from a table/partition, make sure you shrink them afterwards so the extents are marked as free. IF you are using recyclebin, try purging it.
hth
Alan.-
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 05 2012 - 20:43:10 CDT