In Oracle RDBMS databases, data is stored logically as tablespaces and physically as data files. However, accidental adding of a wrong datafile to the tablespace, or the deletion of a datafile may make Oracle unable to startup its database. Thus, you have to remove and/or drop the datafiles from the tablespace to keep using the Oracle database.

Although the Oracle interface does not have an easy way of doing this, there are a few roundabout solutions to the problem.

Completely dropping the tablespace with all its datafiles

The easiest way is to drop the entire tablespace together with its datafiles. If you no longer need the data contents of the tablespace, then the following command will drop the tablespace, the datafile, and the tablespace’s contents from the database. All objects contained in that tablespace are permanently removed and the problem solved.


Note: Always perform a backup – it’s good to check and identify the files that have been dropped from the database in this process.

Checking the number of data files in a tablespace

If you wish to drop only part of the datafiles of the tablespace, first export the entire data. Then, you can drop the tablespace with “DROP TABLESPACE” command and recreate it with the required datafile(s) by importing them from your backup.

If datafiles are missing after a recovery process (or an accidental deletion), then make the datafile offline by ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP. This will enable Oracle to startup the database without errors.

Resizing a Datafile to Minimum Size

Another way to effectively drop a datafile is to shrink its size. Provided there are no extents to the datafile, you can resize it to a small size (2 blocks). This solution does not remove the datafile from tablespace nor delete it physically, but reduces the risk as no data will be truncated – only empty blocks are lessened. The file becomes unusable, and takes up almost zero disk space. To resize, use the query:

ALTER DATABASE FILE ‘<datafile name>’ RESIZE 8M;