【翻譯自mos文章】從tablespace中 刪除空的datafile時報ora-03262，tablespacedatafile

【翻譯自mos文章】從tablespace中 刪除空的datafile時報ora-03262，tablespacedatafile

參考自：

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_tsdatafile '/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_tsdatafile '/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 02 2 13 3 24 4 45 5 56 6 67 7 88 8 99 9 1010 10 1111 11 13FILE# RELFILE# TS#---------- ---------- ----------12 12 1413 13 1414 14 1515 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_tsdatafile '/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_tsdatafile '/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 02 2 13 3 24 4 45 5 56 6 67 7 88 8 99 9 1010 10 1111 11 13FILE# RELFILE# TS#---------- ---------- ----------12 13 1413 14 1414 14 1515 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。