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 different procedure as per Oracle release

Procedure to drop the datafile till 10gR2

There is no direct SQL statement to drop datafiles 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 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 datafile 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 restriction also as given below

1) The database must be open.

2) 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.

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 datafiles 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 get missing,you get 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 backup, then if it is index datafile,then we can recreate all index again. If it is having table segment, we can drop the segment and recreate that table from data from some test database

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

Related Articles

How to add datafile to tablespace in Oracle

Tablespace Name ,Size ,Free space,datafiles ,growth in Oracle Database

What you need to know about create tablespace statement in Oracle

How to shrink the datafile in Oracle

Leave a Reply