This system is Oracle 11.2.0.3.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?
Best Answer
Oracle here warn you from dropping
datafile
because it contain amount of data, to make sure thedatafile
is completely empty please execute the following commandif the datafile is completely empty, then you have one of the two option:
1) reboot the database ( sometime it help)
2) move all the data from one
tablespace
to another one, then drop thetablespace
and rename the new one