Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

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 OFFLINE DROP command. This month I attempt to erase the confusion surrounding this command 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.

Database recovery (scenario 2) is beyond the scope of this months article. I will attempt to discuss scenario 1.

Oracle unfortunately does not provide an interface for dropping datafiles in the same way you would drop a schema object such as a table or a user. Once the DBA creates a datafile for a tablespace, the datafile cannot be removed, although this article discusses several workarounds. As with any critical operation like dropping datafiles, ensure you have a full backup of the database.

Determining how many datafiles make up a tablespace

  SELECT
      file_name
    , tablespace_name 
  FROM 
      dba_data_files 
  WHERE 
      tablespace_name ='<name of tablespace>';

Scenario 1

   DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

Scenario 2

   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

  SELECT
      file_name
    , tablespace_name 
  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>'

Once the export is done, issue the DROP TABLESPACE <tablespace name> INCLUDING CONTENTS.

Note that this PERMANENTLY removes all objects in this tablespace. Delete the datafiles belonging to this tablespace using the operating system (see the comment above about possible problems in doing this) Recreate the tablespace with the datafile(s) desired, then import the objects into that tablespace. (This may have to be done at the table level, depending on how the tablespace was organized.)

NOTE: The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command 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 the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.
• 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; command to make the file smaller than 5 Oracle blocks. If the datafile is resized 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.