Datafile seems empty, but dropping raises "ORA-03262: the file is non-empty"
Trying to drop a datafile:
So it looks empty after all; all blocks should be free space. A closer look:SQL> alter tablespace prod drop datafile 13;
alter tablespace prod drop datafile 13
*
ERROR at line 1:
ORA-03262: the file is non-empty SQL> select * from dba_extents where file_id = 13;
no rows selected
SQL> select * from (select file_id, block_id, blocks, block_id - lag(block_id+blocks) over (order by block_id) as gap
from sys.dba_free_space where file_id=13) where rownum<9;
FILE_ID BLOCK_ID BLOCKS GAP
---------- ---------- ---------- ----------
13 5 4
13 9 4 0
13 13 4 0
13 17 4 0
13 21 4 0
13 25 4 0
13 29 4 0
13 33 4 0
8 rows selected.
SQL> select * from (select block_id, blocks, block_id+blocks as end, block_id - lag(block_id+blocks) over (order by block_id) as gap
0