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

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.