ORA-03264 cannot drop offline datafile of locally managed tablespace

On one of my customer’s site, I was told that they want to drop a datafile which is offline for past one year. One year ago some novice person tried to add the datafile with a name which was not correct according to him and while trying to rename it,
he actually took datafile offline. Fortunately this datafile did not contain any allocated extent inside it and was empty and hence application never faced any issue.

Since the archived log files for past whole year not available, recovery of this file was not possible and only option was to drop this datafile

According to MOS notes 1050261.1, we cannot drop an offline datafile for locally managed tablespaces. To drop a datafile, it should be empty, not the first file of the tablespace, should not be from a read-only tablespace and should not be off line. So I was left with following options.

1) Try performing recovery of datafile. In this case, this datafile was offline for past one year and no archives were available for the whole year and hence this option was not supposed to work for me.

2) Use transportable tablespace option to export the entire tablespace, then drop the tablespace and, then import the tablespace back. Unfortunately this tablespace was not self-contained (a tablespace is self-contained if none of its objects are dependent on any other object outside the tablespace), so this option was also not possible for me.

3) Create a tablespace for temporarily holding the segments of this tablespace, move all segments to some this new “temporary” tablespace, drop this tablespace, re-create the tablespace and move back the segments.

So I adopted option 3. There were total 74 segments (tables + indexes) and total segments size of this tablespace was around 6G. To accomplish this, following steps were followed

Created a new tablespace SQL> create tablespace tempdata ‘d:\tempdata01.dbf’ size 10g; Created script using following commands for the movement of segments to new TEMPDATA tablespace SQL> select 'alter table '||owner||'.'|| segment_name||' move tablespace tempdata;' from dba_segments where tablespace_name='DATA' and segment_type='TABLE' order by owner; SQL> select 'alter index '||owner||'.'|| segment_name||' rebuild tablespace tempdata;' from dba_segments where tablespace_name='DATA' and segment_type='INDEX' order by owner; Executed the script created above for segments movement. Once done, I dropped the existing DATA tablespace and recreated it SQL> DROP TABLESPACE data INCLUDING CONTENTS AND DATAFILES SQL> CREATE TABLESPACE data DATAFILE ‘d:\oracle\oradata\<db_name>\data01.dbf’ size 10g; Again used following statements to get the script to move the segments back SQL> select 'alter table '||owner||'.'|| segment_name||' move tablespace data;' from dba_segments where tablespace_name='TEMPDATA' and segment_type='TABLE' order by owner; SQL> select 'alter index '||owner||'.'|| segment_name||' rebuild tablespace data;' from dba_segments where tablespace_name='TEMPDATA' and segment_type='INDEX' order by owner; Drop the TEMPDATA tablespace. SQL> DROP TABLESPACE tempdata INCLUDING CONTENTS AND DATAFILES;