DROP Datafile And Its Restrictions [ID 781225.1]
		修改时间 22-MAR-2011 类型 HOWTO 状态 PUBLISHED

In this Document
Goal
Solution

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 - Release: 10.1 to 11.1
Information in this document applies to any platform.

Goal

How to drop a datafile from a tablespace and what are its restrictions ?

.

Solution

From 10g onwards we have a option to drop a datafile from a tablespace. We can 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.

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

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

This is equivalent to the following statement:

Restrictions for Dropping 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 Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

[@more@]