Remove obsolete Datafiles from a Tablespace

Overview
Adding datafiles to an existing tablespace is a very simple and straight forward task. But if you want to remove one or more file(s) from a tablespace for any reason it is not as simple as you might imagine.
Initial Setting
For test purposes I need some additional space in an existing tablespace. After the tests I want to cleanup the tablespace too: I would like to remove the obsolete datafile from the tablespace to save diskspace. Commands such as ...
ALTER DATABASE DATAFILE 'file_name' OFFLINE DROP;
... are only disabling the datafile for further usage. But the datafile still remains in the list of datafiles and can not be removed from file system which is not what I really wanted. The following shows you the expensive way how to do this!
Step by Step
1.
Create new tablespace: The tablespace is needed temporarily to get all the remaining objects. The parameters must fit the existing tablespace.
CREATE TABLESPACE new_tablespace DATAFILE 'file_name' SIZE xxxK REUSE AUTOEXTEND OFF [EXTENT MANAGEMENT LOCAL UNIFORM SIZE xxxK] PERMANENT ONLINE;
2.
Move all objects from old tablespace to new tablespace: All remaining objects are move from the old tablesapce (with the obsolete datafile(s)) to the new created tablespace using corresponding commands for tables, partitions, or indexes.
ALTER TABLE table_name MOVE TABLESPACE new_tablespace;
ALTER TABLE table_name MOVE PARTITION partition_name TABLESPACE new_tablespace;
ALTER INDEX index_name REBUILD TABLESPACE new_tablespace;
3.
Drop old tablespace: Now the old tablespace should be empty and can be removed. This deletes the obsolete datafile(s) from data dictionary too! To be sure removing an empty tablespace you must omit the option INCLUDING CONTENTS.
DROP TABLESPACE old_tablespace;
4.
Recreate old tablespace: The original tablespace can be created again, reusing all the existing OS files minus the obsolete datafile(s).
5.
Move all objects back from new tablespace to old tablespace: All objects in the temporary tablespace must be moved back to the original tablespace.
6.
Drop new tablespace: The temporary tablespace (created in step 1) can be deleted.
7.
Check for invalid or unusable objects: Even if all objects are valid you might have INVALID or UNUSABLE indexes. Rebuild them!
-- Select invalid objectsCOLUMN object_name format a32
SELECT owner, SUBSTR(object_name, 1, 32) "OBJECT_NAME", status
FROM dba_objects
WHERE status != 'VALID'
AND owner NOT IN ('SYS','ORDSYS','ORDPLUGINS')
ORDER BY 1, 2;
---- Select invalid indexes
SELECT owner, table_name, index_name, status
FROM dba_indexes
WHERE status != 'VALID'
AND partitioned != 'YES'
AND owner NOT IN ('SYS','ORDSYS','ORDPLUGINS')
ORDER BY 1, 2, 3;
---- Select unusable partitioned indexes
SELECT index_owner, partition_name, index_name, status
FROM dba_ind_partitions
WHERE status != 'USABLE'
AND index_owner NOT IN ('SYS','ORDSYS','ORDPLUGINS')
ORDER BY 1, 2, 3;
8.
Cleanup OS file(s): Finally you can do what you initially wanted: Remove the obsolete datafile(s) from file system
Conclusion
Adding datafiles to an existing tablespace is very easy, removing is somehow tricky and error-prone. Much easier is the handling of whole tablespaces: In this case I would prefer adding some tablesapces for test purposes, do the tests, and remove the whole tablespaces afterwards