alter tablespace temp&…

目前测试再10G（10.2.0.4.0）版本里 drop tempfile 后物理文件依然存在，查看文档应该是自动删除的，

 

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.

原因待查

在11G(11.2.0.1)中 drop tempfile 后物理临时文件自动删除

 

10G中drop tempfile时，如果应用系统未关闭，可能还需要读取临时文件的内容，手工删除或移动临时文件时可能引发应用系统错误，数据丢失，记录一下

 

SQL*Plus: Release 10.2.0.4.0 - Production on 星期一 11月 10 11:46:27 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\t
emp02.dbf' size 1024M;

表空间已更改。

SQL> alter tablespace temp drop tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\
temp02.dbf';

表空间已更改。

SQL>

 

 参考文档：

DROP Datafile And Its Restrictions (文档 ID 781225.1)

转到底部

---

In this Document Goal Solution Applies to: Oracle Database - 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. ***Checked for relevance on 25-Oct-2013*** 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. 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; 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.