This system is Oracle 220.127.116.11.0, using ASM. We had an issue where there seemed to be a corruption issue of some kind – every time a row tried to get inserted into the table, we'd get the following:
ORA-01115: IO error reading block from file (block # ) ORA-01110: data file 23: '<filepath>' ORA-15081: failed to submit an I/O operation to a disk
Luckily, this is a test system and there happened to be only one table on data file 23, according to
dba_extents. I had the team drop the table and recreate it in another tablespace for the moment.
However, now I'd like to drop the datafile that was reporting the issue.
select count(*) from Dba_Extents where file_id = 23; reports 0 – so there seem to be no extents using that data file. However,
ALTER TABLESPACE tablespaceName DROP DATAFILE '<filepath>'; doesn't work. I get the following:
Error report - SQL Error: ORA-03262: the file is non-empty 03262. 00000 - "the file is non-empty" *Cause: Trying to drop a non-empty datafile *Action: Cannot drop a non empty datafile
How can I drop this file?