Oracle offline drop datafile

ALTER DATABASE DATAFILE  OFFLINE DROP

Below is my understanding and testing.

This command will not drop the datafile (from both OS and database ) , it will simply take the datafile offline and Oracle will no longer attempt to access it. Offlining the datafile is typically used when your intention is to drop the tablespace. This will also give you the ability to recover the datafile at a later date, e.g recover a datafile (within short period, assuming no REDO log group being recycled yet) in noarchivelog mode.

 SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                     
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS        
---------- ------------------------------ ---------- ---------- ---------     
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS    
------------ --- ---------- ---------- ------------ ---------- -----------    
ONLINE_                                                                       
-------                                                                       
/oracle/UAT/sapdata1/system_1/test1.dbf                                       
        74 TESTBS                           20971520       2560 AVAILABLE     
          74 NO           0          0            0   20905984        2552    
ONLINE                                                                        
                                                                              
/oracle/UAT/sapdata1/system_1/test2.dbf                                       
        75 TESTBS                           20971520       2560 AVAILABLE     
          75 NO           0          0            0   20905984        2552    
ONLINE                                                                        
                                                                              
                                                 

SQL> alter database datafile 75 offline drop;

Database altered.

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-00376: file 75 cannot be read at this time
ORA-01110: data file 75: '/oracle/UAT/sapdata1/system_1/test2.dbf'


SQL> recover datafile 75;
Media recovery complete.

SQL>  alter database datafile 75 online;

Database altered.




 References:

"How to drop a datafile ?" 

 Oracle Drop Datafile