删除单个数据文件的疑问

swgsw

操作系统：OEL5.5 64bit
数据库版本：11.2.0.1 64bit
类型：单实例


问题如下：

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 30 19:43:15 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2345
Current log sequence           2347

SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> set line 120
SQL> select file_id,file_name,tablespace_name from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /app/backup/DQBX_TABLESPACE_plus/dqbx_plus1        DQBX
         6 /app/oracle/oradata/ora11gR2/dqbx.dbf              DQBX
         7 /app/oracle/oradata/ora11gR2/sisp.dbf              SISP
         2 /app/oracle/oradata/ora11gR2/sysaux01.dbf          SYSAUX
         1 /app/oracle/oradata/ora11gR2/system01.dbf          SYSTEM
         8 /app/oracle/oradata/ora11gR2/ts_dqsjzh.dbf         TS_DQSJZH
         3 /app/oracle/oradata/ora11gR2/undotbs01.dbf         UNDOTBS1
         4 /app/oracle/oradata/ora11gR2/users01.dbf           USERS
         5 /app/oracle/oradata/ora11gR2/users02.dbf           USERS
        10 /app/oracle/oradata/ora11gR2/USERS01.dbf           USERS01

11 rows selected.

SQL> !ls -l /app/oracle/oradata/ora11gR2/*.dbf
-rw-r----- 1 oracle oinstall 15728648192 06-30 19:07 /app/oracle/oradata/ora11gR2/dqbx.dbf
-rw-r----- 1 oracle oinstall  1073750016 06-30 19:07 /app/oracle/oradata/ora11gR2/sisp.dbf
-rw-r----- 1 oracle oinstall  1048584192 06-30 19:37 /app/oracle/oradata/ora11gR2/sysaux01.dbf
-rw-r----- 1 oracle oinstall  2527076352 06-30 19:37 /app/oracle/oradata/ora11gR2/system01.dbf
-rw-r----- 1 oracle oinstall  1252007936 06-29 22:07 /app/oracle/oradata/ora11gR2/temp01.dbf
-rw-r----- 1 oracle oinstall   104865792 06-30 19:07 /app/oracle/oradata/ora11gR2/ts_dqsjzh.dbf
-rw-r----- 1 oracle oinstall  2149588992 06-30 19:37 /app/oracle/oradata/ora11gR2/undotbs01.dbf
-rw-r----- 1 oracle oinstall  5746204672 06-30 19:07 /app/oracle/oradata/ora11gR2/users01.dbf
-rw-r----- 1 oracle oinstall   104865792 06-30 19:07 /app/oracle/oradata/ora11gR2/USERS01.dbf
-rw-r----- 1 oracle oinstall  7516200960 06-30 19:07 /app/oracle/oradata/ora11gR2/users02.dbf


SQL> col segment_name for a20
SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents where file_id=5;

no rows selected


SQL> alter tablespace users drop datafile 5;
alter tablespace users drop datafile 5
*
ERROR at line 1:
ORA-03262: the file is non-empty

TiMi

lz，

select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents where file_id=5;

和 alter tablespace users drop datafile 5;

间隔多长时间，是否有别的会话在file_id 5上存放了数据

Maclean Liu(刘相兵

[oracle@mlab2 admin]$ oerr ora 3262
03262, 00000, "the file is non-empty"
// *Cause:  Trying to drop a non-empty datafile
// *Action: Cannot drop a non empty datafile

ODM FINDING:

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


CAUSE

The relative file no of the datafile to be dropped is duplicated for other datafiles from different tablespaces. The cause is that 'alter tablespace ... drop datafile ...' checks for rfile# objects instead of absolute file objects (file#). The other datafiles with this rfile# belong to different tablespaces and some of them are not empty.

The issue has been investigated in Bug 12735162: CANNOT DROP EMPTY DATAFILE FROM TABLESPACES WITH DUPLICATED RELATIVE FILE NO
SOLUTION

The bug is fixed in 12.1. There is also a request to include the fix in 11.2.0.3 currently being worked.

1. The workaround is to move all the objects from the tablespace where the empty datafile is and then drop the tablespace including contents and datafiles.

2. To fix the issue, please check if a patch is available for your release. Otherwise, file an SR with Oracle Support to request a patch.
REFERENCES

Maclean Liu(刘相兵

尝试不要指定 datafile 5;, 而指定 文件位置