Drop datafile

[Prof]

How I can drop datafile from tablespace?

[rsuri]

alter database datafile xxx offline drop;


- Rajeev

[sreddy]

> alter database
datafile 'filename' offline drop ;

be careful and make sure you really want to do that. I was at work for 36 hours in messing up with one of the datafile.To be safe, take your tablespace which contains the datafile offlline.
I think this z the task you want to do when the database is closed mode/restricted mode.

[omegamark]

Boss u can never drop a datafile in Oracle.To drop a datafile
the only way is to drop the tablespace and delete the datafile manually.

the option of offline drop is for other purpose.Offline drop does not drop the datafiles.it is for making the datafile offline in noarchive mode for maintence purpose .pls read the manuals carefully.

[marist89]

I concur. You can not drop a datafile without dropping the tablespace. My suggestion would be:
1. move all objects in tablespace x to tablespace y
2. drop tablespace x
3. rm files
4. create tablespace x with the datafiles you want
5. move everything back to x from y
6. drop y
7. rm files

[sreddy]

Omegamark is Right. I just recollected what we have gone thru, you can keep it offline, but datafile will be part of the database and tablespace until tablespace is dropped... you can't bring database up once you delete the datafile physically.