oracle 10g R2 drop empty datafile

在oracle 10g以前要是对某个表空间误加了个数据文件,想删除掉这个时候是不行的,通常的做法是offline或者resize 到
一个很小的值,这对我们管理带来很多不方便, 10g 就可以了,以下是测试验证过程:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create tablespace test;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF
SQL> alter tablespace test add datafile;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q4RY8_.DBF
6 rows selected.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q4RY8_.DBF
6 rows selected.
SQL> alter tablespace test drop datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\
DATAFILE\O1_MF_TEST_422Q4RY8_.DBF';
Tablespace altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF
SQL>
2.oracle9i 测试
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 7 15:56:30 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> create tablespace test datafile 'd:\test01.dbf' size 5M;
Tablespace created.
SQL> alter tablespace test add datafile 'd:\test02.dbf' size 5M;
Tablespace altered.
SQL> alter tablespace test drop datafile 'd:\test02.dbf';
alter tablespace test drop datafile 'd:\test02.dbf'
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL>