How to DROP a Datafile from a Tablespace

Task: You have just mistakenly added a file to a tablespace, and now want to remove it.

How: Starting with Oracle version 10.2 and higher

You can now alter tablespace drop datafile (except the first datafile of a tablespace)

--drop datafile

ALTER TABLESPACE example DROP DATAFILE '*path*/example_03.dbf'; 

--drop tempfile

ALTER TABLESPACE emp DROP TEMPFILE '*path/temp03.dbf'; 

=

ALTER DATABASE TEMPFILE '*path/temp03.dbf' DROP INCLUDING DATAFILES;

Restrictions for Dropping Datafiles and tempfiles:

· The database must be open;

· If a datafile is not empty, it cannot be dropped; If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

· You cannot drop the first or only datafile in a tablespace, which means that DROP DATAFILE cannot be used with a bigfile tablespace;

· You cannot drop datafiles in a read-only tablespace;

· You cannot drop datafiles in the SYSTEM tablespace;

· If a datafile in a locally managed tablespace is offline, it cannot be dropped.