Removing Datafiles from a Tablespace

There are serveral ways to remove a datafile, one is with an offline drop and other is using alter tablespace. The offline drop is to be used inconjunction with drop tablespace, that is is expected the tablespace will also be dropped. If the intention is just to drop one or more datafiles that is part of a tablespace then alter tablespace drop datafile must be used which was introduced with 10gR2. There are several constraints to dropping a datafile they are (from Oracle documentation)
The following are restrictions for dropping datafiles and tempfiles:
The database must be open.

If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.

You cannot drop datafiles in a read-only tablespace.

You cannot drop datafiles in the SYSTEM tablespace.

If a datafile in a locally managed tablespace is offline, it cannot be dropped..

Situation is that there are segment(s) where extents are spread across multiple datafiles and now it is decided to move all the extents into single datafile.

SELECT x.*,  COUNT(*) FROM
  (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
    segment_name ,    segment_type ,    tablespace_name
  FROM dba_extents  WHERE tablespace_name='TEST'  ) x
group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          19
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULT     TABLE    TEST           1
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULINDX  INDEX    TEST          12

For testing this could be created with step 1-4 in an earlier post.

To drop the datafile in the flash diskgroup move the segments (table and indexes) to another tablespace either through alter table .. move (there are other ways too) and alter index rebuild .. tablespace (just one way to move an index). Depending on the number of segments in the datafile that is in flash diskgroup this could take some time and if logging is used could generate lot of redo.

Once all the extents are moved out drop the datafile from the tablespace. If the datafile is not empty following error will be shown

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';
alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919'
*
ERROR at line 1:
ORA-03262: the file is non-empty

To move segments

SQL> alter table fult move tablespace users;

Table altered.

SQL> alter index fulindx rebuild online tablespace users;

Index altered.

After the move verify no extents are on the datafiles in question

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

no rows selected

Drop the datafile

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';

Tablespace altered.

Move the segments back into the tablespace which now has only the desired datafile(s).

SQL> alter table fult move tablespace test;

Table altered.

SQL> alter index fulindx rebuild online tablespace test;

Index altered.

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          20
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULINDX  INDEX    TEST          12