How to Drop a datafile from Tablespace in Oracle

Sometimes we need to drop the datafile or tempfile from the tablespace in Oracle.  We have the different procedures as per the Oracle release

Procedure to drop the datafile till 10gR2

There is no direct SQL statement to drop data files from a tablespace. In that case, we need to drop the tablespace after all data has been moved to a new tablespace.

1. Create a new oracle 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.

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;

Procedure to drop the datafile till 10gR2

With 10gR2, we can directly drop the datafile from tablespace if the data file is empty i.e when no extents are allocated from it. You can check if the datafile is empty using the below command

SELECT owner, segment_name
FROM dba_extents a, dba_data_files b
WHERE a.file_id = b.file_id
AND b.file_name = '<datafile name>'

There are  some restrictions also as given below

1) The database must be open.

2) If a data file 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.

3)You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.

4) You cannot drop datafiles in a read-only tablespace.

5)You cannot drop data files in the SYSTEM tablespace.

6)If a datafile in a locally managed tablespace is offline, it cannot be dropped.

So

Syntax

ALTER TABLESPACE DROP DATAFILE | TEMPFILE command:

ALTER TABLESPACE example DROP DATAFILE '+DATA/example_3.f';

ALTER TABLESPACE TEMO DROP TEMPFILE '/+DATA/temp2.dbf';

How to offline drop the datafile

Sometimes a datafile gets missing, you get an issue opening up the datafile.

Then you can use offline drop to the control file from checking it

alter database datafile '/u01/oracle/oradata/ex_01.dbf' offline drop;

The file can still be restored and recovered and back in operation.

Or if don’t have any oracle database backup, then if it is index datafile, then we can recreate all indexes again. If it is having a table segment, we can drop the segment and recreate that oracle table from data from some test database

 Hope you like this article on How to Drop a datafile from Tablespace in Oracle

Related Articles
alter tablespace add datafile : Check out this post on How to add datafile to tablespace in Oracle, add tempfile to temporary tablespace,how to add datafile in ASM
How to check Tablespace in Oracle : Learn about How to Check Tablespace in Oracle, tablespace free space,tablespace growth information,tablespace size ,associated datafiles ,tablespace size
how to change default tablespace in oracle :Default tablespace are the tablespace where the objects are created when no tablespace name is specified by users.Find out how to check default tablespace
How to check temp tablespace in Oracle :This article is about temp tablespace in Oracle, resize the tempfile, drop the tempfile,find the temp usage by Session
shrink datafile in Oracle : Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297