In fact Oracle does not provide an interface for dropping datafiles like you could drop a schema and its object in case of table, a view, a user, etc. Once you add a datafile in the tablespace then the datafile cannot be removed but in some case you need do it then you can perform some work to find closer results.
How to Deal different scenario Need to remove Datafile:
Select file_name, tablespace_name from dba_data_files where tablespace_name =’SDH_TIMS_DBF’;
If the datafile you want to remove is the only datafile in that tablespace then simply drop the entire tablespace:
DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;
Note: Before performing certain operations such as taking tablespaces/datafiles offline, and trying to drop them, ensure you have a full backup.
The DROP TABLESPACE command removes the tablespace, the datafiles, and its contents from data dictionary. Oracle will no longer have access to ANY object that was contained in this tablespace. The physical datafile must then be removed using an operating system command (Oracle NEVER physically removes any datafiles).
If you have more than one datafile in the tablespace and you want to keep the objects of first datafile then you must export all the objects you want to keep then Drop the tablespace.
Select owner,segment_name,segment_type from dba_segments where tablespace_name='<name of tablespace>’
Note: Make sure you specify the tablespace name in capital letters.
Now Re-create the tablespace with the desired datafiles then import the objects into that tablespace.
If you just added the datafile and Oracle has not yet allocated any space within this datafile, then you can resize to make the datafile smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.
ALTER DATABASE DATAFILE <filename> RESIZE;
Here we are not including the OFFLINE DROP command because is not meant to allow you to remove a datafile.
ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP;
ALTER DATABASE DATAFILE <datafile name> OFFLINE; –in archivelog mod
What the above command really means is that you are offlining the datafile with the intention of dropping the tablespace. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup. The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.