概述:现需要删除一个数据文件,查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重复的问题。