当前位置:  数据库>oracle

从tablespace中 删除空的datafile时报ORA-03262

    来源: 互联网  发布时间:2017-06-14

    本文导语: 参考自: 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 ...

参考自:

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

conn / as sysdba

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;

/*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 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 130

SQL> 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。


    
 
 

您可能感兴趣的文章:

 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle tablespace表空间调整


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,