Remove obsolete Datafiles from a Tablespace

Christoph Gächter, Akadia AG, Information Technology, CH-3604 Thun
Phone: +41 33 335 86 21 / Fax: +41 33 335 86 25 /
EMail: christoph.gaechter@akadia.com

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.

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!

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 objects
COLUMN 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

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.