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?

  • 3
    Empty the DBA recyclebin first? – Andrew Brennan Jun 17 '14 at 15:25

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

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.

    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.

    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 :)

    ALTER DATABASE DATAFILE 'path' OFFLINE DROP

    • 1
      Can you elaborate on why I need to do that? – rfusca Jun 5 '14 at 19:41

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

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

    • 1
      Definitely cannot drop the tablespace - there's tons of data in other data files for the same table – rfusca Jun 5 '14 at 19:42
    • Your choices are simple, you can recover the datafile, you can offline drop the datafile and contents or you can drop the tablespace. Do you have a current backup of the database? – Gandolf989 Jun 5 '14 at 20:35
    • 2
      He should be able to drop just the datafile if it is empty. This looks a lot like bug 12735162 to me. – Jack Douglas Jun 6 '14 at 18:09

    Your Answer

     
    discard

    By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

    Not the answer you're looking for? Browse other questions tagged or ask your own question.