Re: Help, delete a data file

Organically speaking, multiple files within the same tablespace are a homogeneous region allowing a tablespace to span physical partitions. I would never just delete a file even if I thought/knew it had not been used yet. It certainly has been initialized and made a part of the organic whole.

-Lane

Lutz Birkhahn wrote:

> 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

--
Lane Sharman
Pour chasser le chagrin,
il faut du vin.