If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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)

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.