How to Drop a Datafile from a Tablespace

Contents

• Introduction
• Tablespace / Datafile Example
• Drop Datafile by Oracle Release
• Alternative Scenarios
• About the Author

Introduction

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.

As with any critical operation like dropping datafiles, ensure that you have a full backup of the database before running the statements and commands highlighted in this article.

Tablespace / Datafile Example

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:

SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'name_of_tablespace';

For example:

select tablespace_name, file_name, file_id from dba_data_files where tablespace_name = 'USERS'; TABLESPACE_NAME  FILE_NAME                                                   FILE_ID ---------------- ---------------------------------------------------------- -------- USERS            C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_5YM2N5D0_.DBF         5

To add a 5MB datafile to the USERS tablespace configured with Oracle Managed Files (OMF), use the following:

SQL> alter tablespace users add datafile size 5m; Tablespace altered.

Re-query the data dictionary to determine the name and location of the new datafile:

select tablespace_name, file_name, file_id from dba_data_files where tablespace_name = 'USERS'; TABLESPACE_NAME  FILE_NAME                                            FILE_ID ---------------- --------------------------------------------------- -------- USERS      C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_5YM2N5D0_.DBF    5 USERS      C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF    6   <- New File

Drop Datafile by Oracle Release

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.

Oracle8i

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.

1. Create a new tablespace to hold moved objects.
2. Move all tables to the new tablespace.
3. Move all indexes to the new tablespace.
4. Move all other segments to the new tablespace.
5. Drop the old tablespace using the INCLUDING CONTENTS option.
6. Remove all datafiles belonging to dropped tablespace using OS command.

Oracle9i Release 1

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.

1. Create a new tablespace to hold moved objects.
2. Move all tables to the new tablespace.
3. Move all indexes to the new tablespace.
4. Move all other segments to the new tablespace.
5. Drop the old tablespace using the INCLUDING CONTENTS AND DATAFILES option.

Oracle9i Release 1 introduced the "AND DATAFILES" clause to the DROP TABLESPACEstatement which will automatically remove physical datafiles from the file system.

Oracle9i Release 2

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.

1. Create a new tablespace to hold moved objects.
2. Move all tables to the new tablespace.
3. Move all indexes to the new tablespace.
4. Move all other segments to the new tablespace.
5. Drop the old tablespace using the INCLUDING CONTENTS AND DATAFILES option.

Although datafiles cannot be dropped using a direct SQL statement, tempfiles can be dropped starting in Oracle9i Release 2 using SQL as follows:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' drop including datafiles; Database altered.

Oracle Database 10g Release 1

Use the same procedures documented for Oracle9i Release 2

Oracle Database 10g 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.

SQL> alter tablespace users drop datafile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF'; Tablespace altered.

The next example drops the tempfile O1_MF_TEMP_6LDH8JQB_.TMP which belongs to the TEMP tablespace.

SQL> alter tablespace temp drop tempfile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_TEMP_6LDH8JQB_.TMP'; Tablespace altered.

Note that this is equivalent to the following statement that was introduced in Oracle9i Release 2 to drop tempfiles:

SQL> alter database tempfile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_TEMP_6LDH8JQB_.TMP' drop including datafiles; Database altered.

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.

Oracle Database 11g Release 1

Use the same procedures documented for Oracle Database 10g Release 2

Oracle Database 11g Release 2

Use the same procedures documented for Oracle Database 10g Release 2

Alternative Scenarios

Scenario 1

If the datafile you wish to remove is the only datafile in that tablespace, you can simply drop the entire tablespace using the following:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

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:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;

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.

Scenario 2

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:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

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.

Scenario 3

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 DATAFILEcommand 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:

SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'name_of_tablespace';

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:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'name_of_tablespace';

Now, export all the objects that you wish to keep.

Once the export is done, drop the tablespace:

DROP TABLESPACE name_of_tablespace INCLUDING CONTENTS AND DATAFILES;

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.

Always remember that the ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP 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. If you are running in archivelog mode, you can also use ALTER DATABASE DATAFILE <datafile name> OFFLINE instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

Scenario 4

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.