oracle 10g R2 drop empty datafile

在oracle 10g以前要是对某个表空间误加了个数据文件,想删除掉这个时候是不行的,通常的做法是offline或者resize 到

一个很小的值,这对我们管理带来很多不方便, 10g 就可以了,以下是测试验证过程:

SQL> select * from v$version;

BANNERITPUB个人空间C0RljR o4m
----------------------------------------------------------------
.Q
T!v:G9t8R0Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdITPUB个人空间z)nE7Ab(B K
PL/SQL Release 10.2.0.1.0 - ProductionITPUB个人空间2?#UJ)~+j+X#jq6S
CORE    10.2.0.1.0      ProductionITPUB个人空间_~bMG0?2K
TNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionITPUB个人空间?J+n4S'k*y
NLSRTL Version 10.2.0.1.0 - Production

SQL> create tablespace test;

Tablespace created.

SQL> select name from v$datafile;

NAMEITPUB个人空间 H}piX
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBFITPUB个人空间;]I:u|]*UI3m
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBF

@!?!?2|?V1U%A:B'X0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
,b'O8Z]LFWf0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
lyStaVLM0v \0D:\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;

NAMEITPUB个人空间;Dk"aj|z)I ?J [s
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
5L0ti^,^CF0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBFITPUB个人空间-Z4Y}2m;Z
FgC^
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBFITPUB个人空间y9j.V&@ ya8E
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
R\rGT5S0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBF
t,kok/H0D:\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_NAMEITPUB个人空间"h~0f$_#^E"x@%T
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBFITPUB个人空间`RD5q&y
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBFITPUB个人空间0zY,T5b8S
v8X4c l
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
N,Z!^h2h0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBF
6z[n S:U%tQ1Q0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TEST_422Q3D0H_.DBFITPUB个人空间1w7jR(an.Pm5`
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\
$P6f-iSA;y0DATAFILE\O1_MF_TEST_422Q4RY8_.DBF';

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAMEITPUB个人空间0IF;l,x1`$Ca^*i0A
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_422B1M98_.DBF
6u%T4G;i2e#x]0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_422B1MC7_.DBFITPUB个人空间9Uu8V+_VNV C;HP
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_422B1MFO_.DBF
Thd9k%x0P(Z*P#c/m x0D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_USERS_422B1MNZ_.DBFITPUB个人空间J|epJiI3U
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.

ITPUB个人空间-LV\lBM&m
Connected to:
m%K j`"b
I4_;h0Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
9N*dzk}:L.b\0With the Partitioning, OLAP and Oracle Data Mining options
to;~K^o9UR {m0JServer 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';
H:u/`2@#iD0alter tablespace test drop datafile 'd:\test02.dbf'ITPUB个人空间"YoF'~8l+l
                      *
-f }1w7A"ph
cAO0ERROR at line 1:ITPUB个人空间c)_Z!K0eZ g'fW
ORA-02142: missing or invalid ALTER TABLESPACE option

SQL>