Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Dropping Datafiles

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 Oracle Automatic Storage Management (Oracle ASM) disk group.

The following example drops the datafile identified by the alias example_df3.f in the Oracle ASM disk group DGROUP1. The datafile belongs to the example tablespace.

ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';

The next example drops the tempfile lmtemp02.dbf, which belongs to the lmtemp tablespace.

ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

This is equivalent to the following statement:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP 
     INCLUDING DATAFILES;

See Oracle Database SQL Language Reference for ALTER TABLESPACE syntax details.

Restrictions for Dropping Datafiles

The following are restrictions for dropping datafiles and tempfiles: