|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
|PDF · Mobi · ePub|
You use the
TEMPFILE clauses of the
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
ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';
The next example drops the tempfile
lmtemp02.dbf, which belongs to the
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;
Note:If there are sessions using a tempfile, and you attempt to drop the tempfile, then an error is returned, and the tempfile is not dropped. In this case, the tempfile is taken offline, and queries that attempt to use the tempfile will fail while the tempfile is offline.
See Oracle Database SQL Language Reference for
TABLESPACE syntax details.
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.
DATAFILE cannot be used with a bigfile tablespace.
You cannot drop datafiles in a read-only tablespace that was migrated from dictionary managed to locally managed. Dropping a data file from all other read-only tablespaces is supported.
You cannot drop datafiles in the
If a datafile in a locally managed tablespace is offline, it cannot be dropped.
See Also:Dropping Tablespaces