Newsletters Archive - 2011
If you would like to know
more about the iDevelopment.info Newsletter, please
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