Re: Help, delete a data file

Corry Retzke wrote:
>
> Cathy Fischer wrote:
> >
> > I have an extra data file allocated to a tablespace that doesn't have anything
> > in it and I want to get rid of it and reclaim the space for something else. Is
> > there a way to do this without dropping the tablespace and re-creating it?
>
> This is by the book, not from experience, as I've never done this in
> practice. I recommend doing this on a test database first.
>

 [...]
> 3) There's a command listed in the 'Oracle Server Reference' manual that
> allows you to drop an individual file. I believe the syntax is:
> alter database drop file 'filespec';

Perhaps you are referring to the 'SQL Language Reference Manual':

   alter database datafile <file> offline drop

But I'm sorry to tell you that this statement does not seem to solve the problem! The manual states that this "takes a data file offline when the database is in noarchivelog mode", no word of *dropping* the data file. I have to admit that I don't see any difference between taking a data file offline, whether the DB is in archivelog or in noarchivelog mode, so I don't understand the manual. But we have tried that statement (in archivelog mode, of course), and it doesn't seem to do anything else than taking the file offline. Finally we dropped the whole tablespace, and recreated it with exp/import of the data.

I think it's funny that even Oracle worldwide customer support suggested that statement when we urgently needed to get rid of an unused data file (cause it was in recovery mode and therefore the *whole* tablespace could not been taken online again, since we did not have the archived redo logs from 6 weeks ago). Does that tell me something about the quality of WCS, or did we overlook something?!? Any further enlightenment welcome!

Bye,
Lutz

-- 
Lutz Birkhahn - Systemadministrator - 02543 / 72-233
Dr. Otto Suwelack Nachf. GmbH & Co, 48727 Billerbeck