When an empty datafile is deleted from tablespace, The ora-03262, tablespacedatafile

When an empty datafile is deleted from tablespace, The ora-03262, tablespacedatafile

Reference:

Cannot Drop Empty Datafile From Tablespace ORA-03262 (Document ID 1353029.1)

Applicable:

Oracle Server-Enterprise Edition-Version: 11.2.0.2 and later [Release: 11.2 and later]
Information in this document applies to any platform.

Symptoms:

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.

Test cases:

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

After reading this article, I cannot delete it because of the bug of Oracle software, that is, the bug of the program.

The relative file no of the datafile to be dropped is the same as the relative file no of other datafiles in other tablespaces.

'Alter tablespace... drop datafile... 'Check rfile # Instead of absolute file objects (file #).
Other datafiles with the same rfile # belong to different tablespaces and are not empty.
The issue has been investigated in Bug 12735162: CANNOT DROP EMPTY DATAFILE FROM TABLESPACES WITH DUPLICATED RELATIVE FILE NO

Solution:

This bug was fixed in 12.1 ). there is also a request to include the fix in 11.2.0.3 currently being worked. --- is there a separate patch in 11.2.0.3 to solve this problem?

1. work und: Remove all objects from the tablespace where empty datafile is located, and then drop the tablespace including contents and datafiles.

2. To fix this issue, check whether your release has a patch. Otherwise, submit an sr to Oracle Support to request a patch.