you cannot drop a datafile from a tablespace unless you are gonna drop the entire tablespace, if you do offline drop that really is
1. I am preparing to drop the file
2. After drop this file I will proceed to drop the tablespace
Add a datafile is easy, drop one is not possible without dropping the tablespace.
Btw offline drop is normally used in database in noarchive log where you have lost a datafile and you want to start the database without getting error (otherwise you will get an error message complaining about missing datafile)
refer to back & recovery doco if you have further doubts
well even in archive log mode you cannot delete a datafile from a tablespace, this is not dependant on archive or noarchive just that in archive log mode you can do alter database datafile xxxx offline but itīs not possible in noarchive, you are forced to do offline drop, in either case you have to get rid of whole tablespace anywayz
You can delete a datafile from tablespace without delete tablespace. After run
alter database datafile 'xxx' offline drop;
You only delete the datafile from your control file, you can use os to delete datafile and later you still can open the database. Keep in mind that datafile name is still in dictionary view. You can't use add datafile to add this datafile anymore. But you can make it online again (if you did not delete datafile in OS). By the way, database objects (such as tables, indexes) will be in any of theses datafiles in this tablespace, if you have any data in this tablespace which have more than one datafiles, even you only dedete one datafile, you may lost your data. If you can lost any data in this tablesace, then you should drop your tablesapce and recreate it later. Then database control file and database dictionary view will give you same information about data files.