fbpx

How to Delete a Datafile in Oracle

green grass field and green tress during day time

You should use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile.

Here are some examples.

ALTER TABLESPACE example1 
DROP DATAFILE '+DGROUP1/example1.dbf';

This other example drops a tempfile.

ALTER TABLESPACE temp 
DROP TEMPFILE '/u02/oracle/data/temp03.dbf';

The database must be open and the datafile should be empty.

You cannot drop datafiles in read-only tablespaces and in the SYSTEM tablespace.