3 - 4 minutes readHow to drop a datafile from a tablespace

Important : Oracle 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.

Reasons why you want to remove a datafile from a tablespace:

• You may have mistakenly added a file to a tablespace.
• You may have made the file much larger than intended and now want to remove it.
• You may be involved in a recovery scenario and the database won’t start because a datafile is missing.

Important : Once the DBA creates a datafile for a tablespace, the datafile cannot be removed. If you want to do any critical operation like dropping datafiles, ensure you have a full backup of the database.

Step: 1 Determining how many datafiles make up a tablespace

To determine how many and which datafiles make up a tablespace, you can use the following query:

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

Case 1

If you have only one datafile in the tablespace and you want to remove it. You can simply drop the entire tablespace using the following:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

The above command will remove the tablespace, the datafile, and the tablespace’s contents from the data dictionary.

Important : Oracle will not drop the physical datafile after the DROP TABLESPACE command. This action needs to be performed at the operating system.

Case 2

If you have more than one datafile in the tablespace, and you wnat to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

Case 3

If you have more than one datafile in the tablespace and you want to remove only one or two ( not all) datafile in the tablesapce or you want 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.

Step: 1 Gather information on the current datafiles within the tablespace by running the following query in SQL*Plus:

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

Step: 2 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>’

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

Step : 4 Once the export is done, issue the

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS.

Step : 5 Delete the datafiles belonging to this tablespace using the operating system.

Step : 6 Recreate the tablespace with the datafile(s) desired, then import the objects into that tablespace.

Case : 4

If you do not want to follow any of these procedures, there are other things that can be done 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 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.

Important : 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.

Important : 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.