Re: Cannot delete a tablespace's datafile

You simply can NOT remove parts of a tablespace from the database. Ever.

The file that you offline dropped will now be sitting there with a status of 'recover' in v$datafile (or some other view that escapes me right now). Not something you want to be running with for an extended period of time whilst doing backups...

The best you can ever do is to resize a datafile to an insignificant size.

Other than that, it's a question of creating a brand new tablespace, moving all the segments across, and then dropping the original one completely.

This particular point, I might just mention, has been discussed on many prior occasions in this group. (I recommend deja.com, even in its post-buy-out format).

Regards
HJR "Huy Vu" <huyv_at_usa.net> wrote in message news:983856777.649868_at_news.aei.ca...
> Hi all,
>
> I tried to delete a datafile1.dbf of tablespace A with the command:
>
> svrmgrl> connect internal
> > startup mount ORCL
> > alter database datafile '/u01/oradata/ORCL/datafile1.dbf' offline drop;
>
> Statement successful
>
> rm /u01/oradata/ORCL/datafile1.dbf
>
> and then
>
> > alter database open;
>
> but the database ORCL still remember the '/u01/oradata/ORCL/datafile1.dbf
 of
> tablespace A and displayed the error message about this datafile
> 'datafile1.dbf' not found. The tablespace A cannot online eventhough other
> datafiles in this tablespace can turn to online.
>
> What is the right command to drop a datafile of a tablespace?
>
> Thanks in advance for any help.
>
> DH.
>
>
Received on Tue Mar 06 2001 - 01:40:32 CST