A ORA-03262 is reported when an empty datafile is deleted from tablespace

After reading this article, I cannot delete it because of the bug of Oraclesoftware, that is, the bug of the program. Relativefileno and others of the datafile to be dropped

After reading this article, I cannot delete it because of the bug of Oracle software, that is, the bug of the program. Relative file no of datafile to be dropped

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 testdocumts including contents and datafiles;

Drop tablespace test2_ts including contents and datafiles;

Create tablespace test1_ts
Datafile '/oracle/oradata/d1v11202/test1_ts.dbf' size 10 M autoextend on maxsize unlimited,
'/Oracle/oradata/d1v11202/test1_2_ts.dbf' size 10 M autoextend on maxsize unlimited;

Create tablespace test2_ts
Datafile '/oracle/oradata/d1v11202/test2_1_ts.dbf' size 10 M autoextend on maxsize unlimited,
'/Oracle/oradata/d1v11202/test2_2_ts.dbf' size 10 M 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 13

FILE # RELFILE # TS #
------------------------------
12 12 14
13 13 14
14 14 15
15 15 15

15 rows selected.
*/

Drop tablespace test1_ts including contents;

Drop tablespace test2_ts including contents;

Host rm/oracle/oradata/d1v11202/test1_ts.dbf

Host rm/oracle/oradata/d1v11202/test1_2_ts.dbf

Host rm/oracle/oradata/d1v11202/test2_1_ts.dbf

Host 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_ts.dbf' size 10 M autoextend on maxsize unlimited,
'/Oracle/oradata/d1v11202/test1_2_ts.dbf' size 10 M autoextend on maxsize unlimited;

Conn/as sysdba

Create tablespace test2_ts
Datafile '/oracle/oradata/d1v11202/test2_1_ts.dbf' size 10 M autoextend on maxsize unlimited,
'/Oracle/oradata/d1v11202/test2_2_ts.dbf' size 10 M 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 13

FILE # RELFILE # TS #
------------------------------
12 13 14
13 14 14
14 14 15
15 15 15

15 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 dtaafile from the first tablespace (where it is the second datafile), which is empty.
In this case, it wocould 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 130

SQL> alter tablespace testforeign 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.