Newsletters Archive - 2011

If you would like to know more about the Newsletter, please email me.

  How to Drop a Datafile from a Tablespace — (07-January-2011)

I have been asked on several occasions about how to drop a datafile from a 
tablespace. Much of the confusion comes from the "ALTER DATABASE DATAFILE 
 OFFLINE DROP" statement. This statement is not meant to allow 
you to remove a datafile. What the statement really means is that you are 
offlining the datafile with the intention of dropping the tablespace.

There are two reasons why a DBA would want to remove a datafile from a 

  * The DBA may have mistakenly added a file to a tablespace, or perhaps made 
    the file much larger than intended and now want to remove it.

  * The DBA may be involved in a recovery scenario and the database won't start 
    because a datafile is missing.

Until Oracle Database 10g Release 2, Oracle did not provide an interface for 
dropping datafiles in the same way you would drop a schema object such as a 
table or a user. Prior to 10gr2, once the DBA created a datafile for a 
tablespace, the datafile could not be removed. This article discusses several 
workarounds to this limitation as well as the new DROP DATAFILE / TEMPFILE 
statement introduced with Oracle 10g R2.

In the following article, I will attempt to erase the confusion surrounding 
this ALTER DATABASE statement and how to successfully drop a datafile from a 

How to Drop a Datafile from a Tablespace

Jeffrey M. Hunter, OCP
Sr. Database Administrator