首页 / 数据库 / MySQL / 从tablespace中 删除空的datafile时报ORA-03262

参考自：Cannot Drop Empty Datafile From Tablespace ORA-03262 （文档 ID 1353029.1）适用于：Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later [Release: 11.2 and later ]
Information in this document applies to any platform.症状：ALTER TABLESPACE ... DROP DATAFILE fails consistently with ORA-3262 "the file
  is non-empty", even if there are no segments （including temporary and recycle bin） in the datafile.测试用例：TESTCASE
~~~~~~~~drop tablespace test1_ts including contents and datafiles;drop tablespace test2_ts including contents and datafiles;create tablespace test1_ts
datafile "/oracle/oradata/d1v11202/test1_1_ts.dbf" size 10M autoextend on maxsize unlimited,
"/oracle/oradata/d1v11202/test1_2_ts.dbf" size 10M autoextend on maxsize unlimited;create tablespace test2_ts
datafile "/oracle/oradata/d1v11202/test2_1_ts.dbf" size 10M autoextend on maxsize unlimited,
"/oracle/oradata/d1v11202/test2_2_ts.dbf" size 10M autoextend on maxsize unlimited;/*
SQL> select file#, relfile#, ts# from file$;FILE# RELFILE# TS#
---------- ---------- ----------
1 1 0
2 2 1
3 3 2
4 4 4
5 5 5
6 6 6
7 7 8
8 8 9
9 9 10
10 10 11
11 11 13FILE# RELFILE# TS#
---------- ---------- ----------
12 12 14
13 13 14
14 14 15
15 15 1515 rows selected.
*/drop tablespace test1_ts including contents;drop tablespace test2_ts including contents;host rm /oracle/oradata/d1v11202/test1_1_ts.dbfhost rm /oracle/oradata/d1v11202/test1_2_ts.dbfhost rm /oracle/oradata/d1v11202/test2_1_ts.dbfhost rm /oracle/oradata/d1v11202/test2_2_ts.dbf/*to duplicate the rfile# */
alter session set events "10120 trace name context forever";create tablespace test1_ts
datafile "/oracle/oradata/d1v11202/test1_1_ts.dbf" size 10M autoextend on maxsize unlimited,
"/oracle/oradata/d1v11202/test1_2_ts.dbf" size 10M autoextend on maxsize unlimited;conn / as sysdbacreate tablespace test2_ts
datafile "/oracle/oradata/d1v11202/test2_1_ts.dbf" size 10M autoextend on maxsize unlimited,
"/oracle/oradata/d1v11202/test2_2_ts.dbf" size 10M autoextend on maxsize unlimited;/*Next steps need to be adapted to your results*/SQL> select file#, relfile#, ts# from file$;FILE# RELFILE# TS#
---------- ---------- ----------
1 1 0
2 2 1
3 3 2
4 4 4
5 5 5
6 6 6
7 7 8
8 8 9
9 9 10
10 10 11
11 11 13FILE# RELFILE# TS#
---------- ---------- ----------
12 13 14
13 14 14
14 14 15
15 15 1515 rows selected./*TS# 14 and 15 each have 2 datafiles:（12, 13） and （14,15） with a common rfile# 14 for file#（13,14）.
You need to see in which ts# the file with rfile# 14 （duplicate） is the first - meaning it cannot be dropped.
Drop file# 15 - drop the datafile from the tablespace where rfile# 14 is the first databafile.
Create a segment in this tablespace which has only 1 datafile left.
Try to drop after that the dtaafile from the first tablespace （where it is the second datafile）, which is empty.
In this case, it would be file# 13 which has the same rfile# 14 as file#14, which is not empty.
*/SQL> alter tablespace test2_ts drop datafile 15;Tablespace altered.SQL> create table test_drop tablespace test2_ts as select * from dual;Table created.SQL> select file#, type#, ts#, block# from seg$ where file# = 14 and type# ！= 3;FILE# TYPE# TS# BLOCK#
---------- ---------- ---------- ----------
14 5 15 130SQL> alter tablespace test1_ts drop datafile 13;
alter tablespace test1_ts drop datafile 13
*
ERROR at line 1:
ORA-03262: the file is non-empty我看了一下这个文章，不能删除的原因是Oracle software的bug，也就是程序的bug。想要drop掉的datafile的 relative file no  与 别的tablespace中的 其他datafile的 relative file no 相重复。"alter tablespace ... drop datafile ..." 检查 rfile# 而不是  absolute file objects （file#）。
有相同rfile#的其他datafile 属于不同的tablespace， 并且，不为空。
The issue has been investigated in Bug 12735162: CANNOT DROP EMPTY DATAFILE FROM TABLESPACES WITH DUPLICATED RELATIVE FILE NO解决方法：该bug在12.1中被解决（fix）.There is also a request to include the fix in 11.2.0.3 currently being worked. ---这句话是不是在11.2.0.3中有单独的patch 能解决这个问题？1. 变通方法是： 从empty datafile所在的表空间中move掉所有的对象，然后 drop the tablespace including contents and datafiles.2. 为了fix 这个问题，请检查针对的你的release是否有patch，否则，请向Oracle Support 提交一个sr  来要求一个patch。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址