Is it Possible to 'DROP' a Datafile from a Tablespace?

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';

FILE_NAME                                 TABLESPACE_NAME

------------------------------------      ---------------

D:\ORACLE\ORADATA\MANOJ\SDH_TIMS01.DBF   SDH_TIMS_DBF

D:\ORACLE\ORADATA\MANOJ\SDH_TIMS02.DBF   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.

OWNER	SEGMENT_NAME	SEGMENT_TYPE
TIMS	GEN_BUYER_OPEN_BALANCE	TABLE
TIMS	GEN_BUYER_PROFILE	TABLE
TIMS	GEN_BUYER_STATEMENT	TABLE
TIMS	GEN_COMPANY_QUANTITY_TYPE	TABLE
TIMS	GEN_CONTRACT_PROFILE	TABLE
TIMS	GEN_CONTRACT_WH_LOCATIONS	TABLE
TIMS	GEN_DEPOSIT_CU	TABLE
TIMS	GEN_DEPOSIT_INSTALLMENT	TABLE
TIMS	STK_ITEM_STATEMENT	TABLE PARTITION
TIMS	USR_SMAN_SALESMAN_FK_I	INDEX
TIMS	AG_DTL_PK	INDEX
TIMS	AG_DTL_AGING_FK_I	INDEX

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...