DBA Tips Archive for Oracle
How to Drop a Datafile from a Tablespace
by Jeff Hunter, Sr. Database Administrator
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 <datafile name> 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. In this article I attempt to erase the confusion surrounding this ALTER DATABASE statement and how to successfully drop a datafile from a tablespace.
There are two reasons why a DBA would want to remove a datafile from a tablespace:
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.
The examples provided in this article were performed on the USERS tablespace using Oracle Database 11g release 2.
To determine how many and which datafiles make up a tablespace, you can use the following query:
To add a 5MB datafile to the USERS tablespace configured with Oracle Managed Files (OMF), use the following:
Re-query the data dictionary to determine the name and location of the new datafile:
An Oracle datafile is a physical part of the database that occupies storage within a file system to store user data. Whenever a datafile gets created or grows, more space on the storage device is required. There are times when a datafile grows beyond an acceptable threshold, an unwanted datafile is created, or a datafile gets created in the wrong tablespace and the datafile needs to be dropped. This section describes the different methods to drop a datafile from a tablespace depending on the Oracle release.
There is no direct SQL statement to drop datafiles from a tablespace in Oracle8i. In that case we need to drop the tablespace after all data has been moved to a new tablespace.
As with Oracle8i, there is no direct SQL statement to drop datafiles from a tablespace in Oracle9i Release 1. In that case we need to drop the tablespace after all data has been moved to a new tablespace.
As with Oracle8i and Oracle9i Release 1, there is no direct SQL statement to drop datafiles from a tablespace in Oracle9i Release 2. In that case we need to drop the tablespace after all data has been moved to a new tablespace.
Although datafiles cannot be dropped using a direct SQL statement, tempfiles can be dropped starting in Oracle9i Release 2 using SQL as follows:
Click here for more information regarding the management of temporary tablespaces.
Use the same procedures documented for Oracle9i Release 2
Starting with Oracle Database 10g Release 2, you can use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. A datafile is considered to be empty when no extents remain allocated from it.
When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Automatic Storage Management (ASM) disk group.
The following example drops an empty datafile O1_MF_USERS_6LD56LRR_.DBF that belongs to the USERS tablespace.
The next example drops the tempfile O1_MF_TEMP_6LDH8JQB_.TMP which belongs to the TEMP tablespace.
Note that this is equivalent to the following statement that was introduced in Oracle9i Release 2 to drop tempfiles:
The following are restrictions for dropping datafiles and tempfiles using the new ALTER TABLESPACE DROP DATAFILE | TEMPFILE command:
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. This 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.
Use the same procedures documented for Oracle Database 10g Release 2
Use the same procedures documented for Oracle Database 10g Release 2
If the datafile you wish to remove is the only datafile in that tablespace, you can simply drop the entire tablespace using the following:
The above statement will remove the tablespace, the datafile, and the tablespace's contents from the data dictionary. All of the objects that where contained in that tablespace are permanently removed. If the datafile was configured using Oracle Managed Files (a feature introduced in Oracle9i Release 1), the physical datafile will be automatically removed from the file system using the DROP TABLESPACE statement.
Oracle9i Release 1 introduced the "AND DATAFILES" clause to the DROP TABLESPACE statement which will automatically remove physical datafiles from the file system:
When not using OMF or the "AND DATAFILES" clause, Oracle will not drop the physical datafile after the DROP TABLESPACE statement. This action would need to be performed manually at the operating system. Depending on the OS (Windows for example), you may have to completely shut down the Oracle instance and associated service before the operating system will allow you to delete the file because of file locks still still being held by Oracle.
If you have more than one datafile in the tablespace, and you do not need the information contained in that tablespace, or if you can easily recreate the information in this tablespace, then use the same statement as above:
Again, this will remove the tablespace, the datafiles, and the tablespace's contents from the data dictionary. Oracle will no longer have access to any object that was contained in this tablespace. You can then use CREATE TABLESPACE and re-import the appropriate objects back into the tablespace.
If you have more than one datafile in the tablespace and you wish to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace. Although Oracle introduced the DROP DATAFILE command in 10gR2, if the datafile is non-empty, it cannot be removed. Start by gathering information on the current datafiles within the tablespace by running the following query in SQL*Plus:
You now need to identify which objects are inside the tablespace for the purpose of running an export. To do this, run the following query:
Now, export all the objects that you wish to keep.
Once the export is done, drop the tablespace:
Note that this PERMANENTLY removes all objects in this tablespace. Recreate the tablespace with the desired datafile(s), then import the objects into that tablespace.
If you do not wish to follow any of the procedures documented in this article, there are other functions that can be performed besides dropping the tablespace.
If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE option.
If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; statement to make the file smaller than 5 Oracle blocks. If the datafile is re-sized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Wednesday, 28-Dec-2011 13:54:41 EST
Page Count: 263115