How to Drop Datafile from Tablespace on Oracle 8i, 9i, 10g, 11g Database?

advertisements

_____________________________________________________________________________________________________________________

Upto Oracle 10gR1 the process of dropping a datafile is a tedious process. From 10gR2 Oracle introduced single command to drop a datafile from the tablespace.  Most of the situations in prior versions (10gR1 and below), DBAs used to reduce the size of the unwanted datafile to a smaller size and keep it with the tablespace itself because of the lengthy process.
Please see the methods to remove the datafiles. Also make sure test these methods before implementing in production to avoid unwanted head aches. J

Oracle 8i

Method is moving all the contents of the tablespace to new tablespace and drop the tablespace which is having the unwated datafile.

1. Create a new tablespace to the database.
2. Move all objects – tables, indexes and other objects to the new tablespace.
3. Drop the old empty tablespace.

Syntax: drop tablespace <tablespace_name> including contents;

4. Remove curresponding all datafiles which belongs to dropped tablespace using OS commands.

Oracle 9i, 10gR1

In Oracle 9i also the method is similar to Oracle 8i. Only difference is Oracle added “including contents and datafiles” clause to the drop tablespace command.

Syntax:

drop tablespace <tablespace_name> including contents and datafiles;

Oracle 10gR2, 11gR1, 11gR2

Oracel introduced single drop datafile, tempfile command from a tablespace in 10gR2. Datafile must be empty which means the datafile doesn’t have any extents allocated on it. Once you drop the datafile the controlfile and data dictionary get updated. Physical files needs to deleted manually using OS commands.

Syntax: alter tablespace <tablespace_name> drop datafile ‘datafilename’;

Alter tablespace <temp tablespace name> drop tempfile ‘tempfilename’;

Example:

alter tablespace USERS drop datafile '+DATA/prod/datafile/ users.516.75500306’;

Tablespace altered.

_____________________________________________________________________________________________________________________

Website Stats