How to Drop Datafile from tablespace

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

How to Drop Datafile from tablespace.

Datafile is physical part of database and it is take storage device /file system to store/save oracle data.

Whenever datafile grow more space needed, sametime unwanted datafile created, sametime datafile created in wrong tablespace then we need to drop datafile.

Below is overview to drop a datafile from tablespace
NOTE: Use below procedure after test on TESTING database.

Oracle 8i
There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents;
6. through OS command remove all datafiles belongs to droped tablespace.

Oracle 9ir1
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;

NOTE: Oracle9ir1 is introduce “and datafiles” clause with drop tablespace statement which remove datafiles from file system.

Oracle 9ir2
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;

But we can drop TEMPFILE through below statement.
SQL> alter database tempfile ‘/u02/oracle/temp01.dbf’ drop including datafiles;
[not tested]

Oracle 10gr1
Same like Oracle 9ir2

Oracle 10gr2
Now in 10gr2 oracle introduce to drop datafile or tempfile from tablespace through single sql command with few restrictions.

From Oracle Documentation:

You 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.

Datafiles:
Alter tablespace data DROP DATAFILE ‘/u02/data01.dbf’;

Tempfiles:
Alter tablespace temp DROP TEMPFILE ‘/u02/temp01.dbf’;

Restrictions for drop datafiles
The following are restrictions for dropping datafiles and tempfiles:
• 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 11gr1
Same like Oracle 10gr2

Reference:
http://www.akadia.com/services/ora_remove_datafile.html
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1396