Why can't I drop this Oracle datafile - even though it seems empty?

Question

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?

Answer 1

Oracle here warn you from dropping datafile because it contain amount of data, to make sure the datafile is completely empty please execute the following command

col tablespace_name format A30 heading tablespace
col file_name format A40
col tot_KB format 99,999,999
col tot_freeKB format  9,999,999
col Kb_max format  9,999,999

select f.tablespace_name
      ,f.file_name
      ,f.bytes/1024 tot_KB
      ,sum(nvl(s.bytes,0)/1024) tot_freeKB
      ,max(nvl(s.bytes,0)/1024) maxextent_freeKB
      ,( (sum(nvl(s.bytes,0)/1024)) / (f.bytes/1024) )*100 Pct_available
from   sys.dba_data_files f
      ,sys.dba_free_space s
where  f.file_id = s.file_id(+)
group by f.tablespace_name, f.file_name, f.bytes/1024
order by 1,2
;

if 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 the tablespace and rename the new one

Answer 2

ORA-3262: the file is non-empty 
    *Cause: Trying to drop a non-empty datafile 
    *Action: Cannot drop a non empty datafile

To drop the datafile following requirement should be met.Datafile -

cannot be offline

must be empty

should not belong to read-only tablespace

cannot be the first file that was created in the Tablespace(Can drop tablespace)

ORA-3262 error occurs when extends from exiting or pre-exiting objects(in recyclebin) exit in the datafile. In your case it must be becuase of the extends still exit in the recyclebin (As you said your team has deleted the table from the datafile# 23). In order to drop the datafile we must purge the recyclebin(Valid only if the Recyclebin is being used) for tha tablespace which has included that datafile.
SQL>PURGE TABLESPACE tablespaceName;
Now try again to drop the datafile.

Answer 3

RMAN allows you to either try to fix the file (recover) or to recreate the file (restore). Before you give up (if ever), you should be at least trying both of these options.

Answer 4

When you delete a table without 'purge' clause, this table "goes" to recyclebin. However, recyclebin its not a different physical space. Oracle just mark those table blocks as "they are part of a table in recyclebin".

Reclcledbin table's blocks aren't shown in dba_extents view, but they count in dba_free_space view.

If you want to check, try this:

USERTEST@oraclr>  select max(rowid) from TABLETEST4;

MAX(ROWID)
------------------
AAAP+vAAJAAAAD+AAG

USERTEST@oraclr>  select dbms_rowid.rowid_to_absolute_fno('AAAP+vAAJAAAAD+AAG','USERTEST','TABLETEST4') relative_fno from dual;

RELATIVE_FNO
------------
           9

USERTEST@oraclr> drop table tabletest4;

Tabla borrada.

USERTEST@oraclr> select object_name, original_name from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$Ke54GemSS6bgUwEAAH/o3g==$0 TABLETEST4


USERTEST@oraclr> select max(rowid) from "BIN$Ke54GemRS6bgUwEAAH/o3g==$0"
  2  ;

MAX(ROWID)
------------------
AAAP+vAAJAAAAD+AAG

USERTEST@oraclr>  select dbms_rowid.rowid_to_absolute_fno('AAAP+vAAJAAAAD+AAG','USERTEST','BIN$Ke54GemRS6bgUwEAAH/o3g==$0') relative_fno from dual;

RELATIVE_FNO
------------
           9

So, the table, is still in the same file :)

Answer 5

ALTER DATABASE DATAFILE 'path' OFFLINE DROP

Answer 6

You probably need to drop the tablespace, not just the datafile.

alter tablespace my_ts offline; drop tablespace my_ts INCLUDING CONTENTS CASCADE CONSTRAINTS;