How To Drop A Datafile In Oracle?

There can be situations where we have mistakenly added datafiles to a tablespace and need to remove it.
Prior to Oracle 10gR2, Oracle did not support dropping of datafiles. The only workaround was to take a export backup of the existing objects in the tablespace. Re-create the tablespace and import the data to the newly created tablespace.
In short, we should drop and recreate the tablespace to fix the issue.

From Oracle 10gR2 onwards , DROP DATAFILE and DROP TEMPFILE statements can be used in conjuction with ALTER tablespace command to drop datafile or tempfile. The datafile must be empty.

SQL> alter tablespace tablespace_name drop datafile ‘datafile.dbf’;

SQL> alter tablespace temp_tbs drop tempfile ‘tempfile.dbf’;

However, a few restrictions are applicable for this command.
>> The database must be open.
>> Cannot drop the datafile if it is not empty. 
>> Cannot drop the first or only datafile in a tablespace.
>> Cannot drop datafiles in a read-only tablespace.
>> Cannot drop datafiles in SYSTEM tablespace.
>> If a datafile in a locally managed tablespace is offline, it cannot be dropped.

Article by : Anju