【Oracle】ORA-03262: the file is non-empty

概述：现需要删除一个数据文件，查dba_extents中已无数据，但仍无法删除，报错ORA-03262: the file is non-empty

分析：

set line 900
col FILE_NAME for a70
select FILE_ID,FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024/1024 GB from dba_data_files order by 1;

   FILE_ID FILE_NAME                                                              TABLESPACE_NAME                AUT         GB
---------- ---------------------------------------------------------------------- ------------------------------ --- ----------
         1 /oracle/orcl11g/datafile/orcl11g/system01.dbf                          SYSTEM                         YES .732421875
         2 /oracle/orcl11g/datafile/orcl11g/sysaux01.dbf                          SYSAUX                         YES  .56640625
         3 /oracle/orcl11g/datafile/orcl11g/undotbs01.dbf                         UNDOTBS1                       YES .322265625
         4 /oracle/orcl11g/datafile/orcl11g/users01.dbf                           USERS                          YES .068359375
         5 /oracle/orcl11g/datafile/orcl11g/users02.dbf                           USERS                          NO  .009765625
         6 /oracle/orcl11g/datafile/orcl11g/test1_1_ts.dbf                        TEST1_TS                       YES .004882813
         7 /oracle/orcl11g/datafile/orcl11g/test1_2_ts.dbf                        TEST1_TS                       YES .004882813
         8 /oracle/orcl11g/datafile/orcl11g/test2_1_ts.dbf                        TEST2_TS                       YES .004882813
         9 /oracle/orcl11g/datafile/orcl11g/test2_2_ts.dbf                        TEST2_TS                       YES .004882813

9 rows selected.

现要删除file 9：

SYS@orcl11g>select count(*) from dba_extents where file_id=9;

  COUNT(*)
----------
         0
        
SYS@orcl11g>alter tablespace TEST2_TS drop datafile 9;
alter tablespace TEST2_TS drop datafile 9
*
ERROR at line 1:
ORA-03262: the file is non-empty

原因：回收站里面还有数据

SYS@orcl11g>select file#, type#, ts#, block# from seg$ where file# = 9 ;

     FILE#      TYPE#        TS#     BLOCK#
---------- ---------- ---------- ----------
         9          5          8        130

查看回收站：

SYS@orcl11g>select OWNER,OBJECT_NAME,ts_name from dba_recyclebin;

OWNER                          OBJECT_NAME                    TS_NAME
------------------------------ ------------------------------ ------------------------------
U1                             BIN$qocbjN1lrXbgU3I4qMBZuA==$0 TEST2_TS

清理回收站（sys用户清理普通用户的回收站）：

purge table u1."BIN$qocbjN1lrXbgU3I4qMBZuA==$0";

或：

purge recyclebin;

再次查看：

SYS@orcl11g>select file#, type#, ts#, block# from seg$ where file# = 9 ;

no rows selected

删除file 9：

SYS@orcl11g>alter tablespace TEST2_TS drop datafile 9;

Tablespace altered.

结论：dba_extents中查看无对象，但是seg$中查出有对象，应该是删除了但还在回收站中，清理了回收站即可正常删除（注意不可删除表空间中的惟一一个数据文件，不可删除第一个数据文件）。
drop table只是将表放到回收站，若要同时清理回收站，则需要用drop table XX purge;

若还删不掉，考虑Cannot Drop Empty Datafile From Tablespace ORA-03262 (Doc ID 1353029.1)中说的关联文件ID重复的问题。