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