如何正确的删除表空间数据文件？
（一）DROP DATAFILE
可以使用如下的命令删除一个表空间里的数据文件：
ALTER TABLESPACE TS_DD_LHR DROP DATAFILE n; --n为数据文件号
ALTER TABLESPACE TS_DD_LHR DROP DATAFILE '/tmp/ts_dd_lhr01.dbf';
关于该命令需要注意以下几点：
① 该语句会删除磁盘上的文件并更新控制文件和数据字典中的信息，删除之后的原数据文件序列号可以重用。
② 该语句只能是在相关数据文件ONLINE的时候才可以使用。如果说对应的数据文件已经是OFFLINE，那么仅针对字典管理表空间（Dictionary-Managed Tablespace，DMT）可用，而对于本地管理表空间（Locally Managed Tablespace，LMT）不能使用，否则会报错“ORA-03264: cannot drop offline datafile of locally managed tablespace”。如果数据文件是RECOVER状态，那么该命令依然不能使用。
③ 不能删除一个表空间中第一个添加的数据文件，否则会报错，形如“ORA-03263: cannot drop the first file of tablespace TS_DD_LHR”。
④ 若一个表空间只包含1个数据文件，则不能删除该数据文件，否则会报错，形如“ORA-03261: the tablespace TS_DD_LHR has only one file”。
⑤ 必须为空，否则会报：ORA-03262: the file is non-empty。值得注意的是，non-empty的含义是有EXTENT被分配给了TABLE，而不是该TABLE中有无ROWS，此时若是使用“DROP TABLE XXX;”是不行的，必须使用“DROP TABLE XXX PURGE;”或者在已经使用了“DROP TABLE XXX;”的情况下，再使用“PURGE TABLE "XXX表在回收站中的名称";”来删除回收站中的该表，否则空间还是不释放，数据文件仍然不能DROP。
⑥ 不能删除SYSTEM表空间的数据文件，否则报错“ORA-01541: system tablespace cannot be brought offline; shut down if necessary”。
需要注意的是，据官方文档介绍说，处于READ ONLY状态的表空间数据文件也不能删除，但经过实验证明，其实是可以删除的。
（二）OFFLINE和OFFLINE DROP的区别
与删除数据文件相似的还有如下的命令（其中的“'FILE_NAME'”也可以用文件号替代）：
ALTER DATABASE DATAFILE 'FILE_NAME' OFFLINE;
ALTER DATABASE DATAFILE 'FILE_NAME' OFFLINE FOR DROP;--FOR也可以省略
需要注意的是，该命令不会删除数据文件，只是将数据文件的状态更改为RECOVER。OFFLINE FOR DROP命令相当于把一个数据文件置于离线状态，并且需要恢复，并非删除数据文件。数据文件的相关信息还会存在数据字典和控制文件中。
对于归档模式而言，“OFFLINE FOR DROP”和“OFFLINE”没有什么区别，因为Oracle会忽略FOR DROP选项。因为OFFLINE之后还需要进行RECOVER才可以ONLINE。
对于非归档模式而言，只能执行“OFFLINE FOR DROP”。若不加FOR DROP选项，则会报错“ORA-01145: offline immediate disallowed unless media recovery enabled”。因为非归档模式没有归档文件来进行RECOVER操作。如果OFFLINE之后，速度足够快，联机Redo日志文件里的数据还没有被覆盖掉，那么在这种情况下，还是可以进行RECOVER操作的。
（三）OS级别删除了数据文件后的恢复
若使用了“ALTER DATABASE DATAFILE N OFFLINE DROP;”命令，则并不会删除数据文件，这个时候可以先ONLINE后再用“ALTER TABLESPACE XXX DROP DATAFILE N;”删除。如果执行“ALTER DATABASE DATAFILE N OFFLINE DROP;”后并在OS级别删除了数据文件，那么首先需要使用“ALTER DATABASE CREATE DATAFILE N AS '/tmp/ts_dd_lhr02.dbf'';”来添加一个数据文件，然后再执行RECOVER并ONLINE后再用“ALTER TABLESPACE XXX DROP DATAFILE N;”命令删除。如果产生的日志文件以及丢失，那么目标文件就不能再恢复了，这个时候只能删除表空间了，命令为：“DROP TABLESPACE XXX INCLUDING CONTENTS AND DATAFILES;”。
示例如下：
1SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;
2Tablespace created.
3SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
4alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
5*
6ERROR at line 1:
7ORA-03261: the tablespace TS_DD_LHR has only one file
8SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;
9Tablespace altered.
10SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
11alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
12*
13ERROR at line 1:
14ORA-03263: cannot drop the first file of tablespace TS_DD_LHR
15SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
16Tablespace altered.
17SYS@ora10g> ! ls -l /tmp/ts_dd_lhr0*
18-rw-r----- 1 oracle oinstall 10493952 Jun 29 14:58 /tmp/ts_dd_lhr01.dbf
19--------------------------------------------------
20SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;
21Tablespace altered.
22SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' offline drop;
23Database altered.
24SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf
25-rw-r----- 1 oracle oinstall 10493952 Jun 29 15:17 /tmp/ts_dd_lhr02.dbf
26SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
27alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
28*
29ERROR at line 1:
30ORA-03264: cannot drop offline datafile of locally managed tablespace
31SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;
32alter database datafile '/tmp/ts_dd_lhr02.dbf' online
33*
34ERROR at line 1:
35ORA-01113: file 9 needs media recovery
36ORA-01110: data file 9: '/tmp/ts_dd_lhr02.dbf'
37SYS@ora10g> recover datafile 9;
38Media recovery complete.
39SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;
40Database altered.
41SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
42Tablespace altered.
43SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf
44ls: cannot access /tmp/ts_dd_lhr02.dbf: No such file or directory
45SYS@orclasm > create table t_ts_dd_lhr tablespace ts_dd_lhr as select * from dual;
46Table created.
47SYS@orclasm > truncate table t_ts_dd_lhr;
48Table truncated.
49SYS@orclasm >
50SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
51alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
52*
53ERROR at line 1:
54ORA-03262: the file is non-empty
55SYS@orclasm > drop table t_ts_dd_lhr;
56Table dropped.
57SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
58alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
59*
60ERROR at line 1:
61ORA-03262: the file is non-empty
62SYS@orclasm > purge recyclebin;
63Recyclebin purged.
64SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
65Tablespace altered.
66SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;
67alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;
68Tablespace created.
69SYS@ora10g>
70Tablespace altered.
71SYS@ora10g>
72SYS@ora10g> alter tablespace ts_dd_lhr read only;
73Tablespace altered.
74SYS@ora10g> select * from dba_tablespaces;
75TABLESPACE_NAME STATUS CONTENTS LOGGING
76------------------------------ --------- --------- ---------
77SYSTEM ONLINE PERMANENT LOGGING
78UNDOTBS1 ONLINE UNDO LOGGING
79SYSAUX ONLINE PERMANENT LOGGING
80TEMP ONLINE TEMPORARY NOLOGGING
81USERS ONLINE PERMANENT LOGGING
82EXAMPLE ONLINE PERMANENT NOLOGGING
83TS10GTEST ONLINE PERMANENT LOGGING
84HHRIS ONLINE PERMANENT LOGGING
85TS_DD_LHR READ ONLY PERMANENT LOGGING
869 rows selected.
87SYS@ora10g>
88SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
89alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
90*
91ERROR at line 1:
92ORA-03263: cannot drop the first file of tablespace TS_DD_LHR
93SYS@ora10g>
94SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
95Tablespace altered.
96SQL>select tablespace_name,file_name from dba_data_files;
97TABLESPACE_NAME FILE_NAME
98----------------- ---------------------------------------------------
99SYSTEM +DATA/oraee/datafile/system.260.870970687
100SYSAUX +DATA/oraee/datafile/sysaux.261.870970721
101UNDOTBS1 +DATA/oraee/datafile/undotbs1.262.870970751
102UNDOTBS2 +DATA/oraee/datafile/undotbs2.264.870970801
103USERS +DATA/oraee/datafile/users.265.870970831
104EXIMTRX +DATA/oraee/datafile/eximtrx.270.871293623
105EXIMUSER +DATA/oraee/datafile/eximuser.271.871293631
106TS_EXIMTRX +DATA/oraee/datafile/ts_eximtrx.272.890144851
107TS_EXIMTRX +DATA/oraee/datafile/ts_eximtrx.273.890146243
108TS_EXIMUSER +DATA/oraee/datafile/ts_eximuser.274.892913553
109TS_EXIMUSER /oracle/app/oracle/product/11.2.0/db/dbs/+DATAA
11011g操作
111SQL>alter tablespace TS_EXIMUSER drop datafile '/oracle/app/oracle/product/11.2.0/db/dbs/+DATAA'; ---->无法识别数据文件
112SQL>alter database datafile 11 offline drop;
113SQL> select file#,status,ts# from v$datafile;
114 FILE# STATUS TS#
115---------- ------- ----------
116 1 SYSTEM 0
117 2 ONLINE 1
118 3 ONLINE 2
119 4 ONLINE 4
120 5 ONLINE 5
121 6 ONLINE 6
122 7 ONLINE 7
123 8 ONLINE 8
124 9 ONLINE 8
125 10 ONLINE 13
126 11 RECOVER 13 ---------->
127SQL> recover datafile 11;
128Media recovery complete.
129SQL> select file#,status,ts# from v$datafile;
130 FILE# STATUS TS#
131---------- ------- ----------
132 1 SYSTEM 0
133 2 ONLINE 1
134 3 ONLINE 2
135 4 ONLINE 4
136 5 ONLINE 5
137 6 ONLINE 6
138 7 ONLINE 7
139 8 ONLINE 8
140 9 ONLINE 8
141 10 ONLINE 13
142 11 OFFLINE 13
143SQL> alter database datafile 11 online;
144Database altered.
145SQL> select file#,status,ts# from v$datafile;
146 FILE# STATUS TS#
147---------- ------- ----------
148 1 SYSTEM 0
149 2 ONLINE 1
150 3 ONLINE 2
151 4 ONLINE 4
152 5 ONLINE 5
153 6 ONLINE 6
154 7 ONLINE 7
155 8 ONLINE 8
156 9 ONLINE 8
157 10 ONLINE 13
158 11 ONLINE 13 ----->数据文件必须在ONLINE状态
15911 rows selected.
160SQL> alter tablespace TS_EXIMUSER drop datafile 11;
161Tablespace altered.
162SQL> select file#,status,ts# from v$datafile;
163 FILE# STATUS TS#
164---------- ------- ----------
165 1 SYSTEM 0
166 2 ONLINE 1
167 3 ONLINE 2
168 4 ONLINE 4
169 5 ONLINE 5
170 6 ONLINE 6
171 7 ONLINE 7
172 8 ONLINE 8
173 9 ONLINE 8
174 10 ONLINE 13
17510 rows selected.
& 说明：
有关删除表空间数据文件的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2124605/
、
