ORA-14404: partitioned table contains partitions in a different tablespace

How to drop an offlined tablespace, if partitions from a partitioned table resides in it [ID 267125.1]
 

Goal

Often the administrators makes common mistake, deleting the files, belonging to the tablespaces with old partitions at OS level, without dropping the appropriate tablespaces.

Once deleted, the only way to start-up the database is to take the coresponding tablespace offline.

From now on, if one try to drop the offlined tablespace, ORA-14404: partitioned table contains partitions in a different tablespace appears.

If in opposite, try to drop the partitioned table ORA-14117: partition resides in offlined tablespace appears.
So, seems no valid way to solve this situation exists.

However, the exchanging of partitions can be used to take the partitions out of the offlined tablespace and subsequently drop the offlined tablespace. Exchanging partitions is dictionary operation, which does not verifies if the partition resides in an on-line or off-line tablespace.

Solution

1) Identify all the partitions, residing in the offlined tablespaces

select owner,segment_name, partition_name,segment_type
from dba_segments
where segment_type='TABLE PARTITION' and 
tablespace_name in(list of the offlined tablespces in upper case)
order by segment_name;

Same apply for subpartitions to identify them follow:

select owner,segment_name, partition_name,segment_type
from dba_segments
where segment_type='TABLE SUBPARTITION' and
tablespace_name in (list of the offlined tablespces in upper case)
order by segment_name;

2) For every of these partitions follow the steps:
2.1) Create an empty table with the same structure as the partitioned one, and in the same schema,  containing the problematic partition

create table TEMP as select * from OWNER.SEGMENT_NAME 
where 1=2;

(here OWNER and SEGMENT_NAME are taken from the query in point 1)

2.2) Exchange the partition from the offlined tablespace with the just created temporary table

alter table OWNER.SEGMENT_NAME
exchange partition PARTITION_NAME with table TEMP;

(here OWNER, SEGMENT_NAME and PARTITION_NAME are taken from the query 1)
This is an example on how a subpartition can be exchanged:

ALTER table OWNER.SEGMENT_NAME exchange subpartition SUBPARTITION_NAME
WITH TABLE temp INCLUDING INDEXES;

Note: Sometimes indexes are also included in the same tablespaces. If you want the Local indexes set on(sub)partition tables be exchanged with the corresponding local or regular indexes, you must use the INCLUDING INDEXES clause. By default, the EXCLUDING INDEXES clause will be set.
2.3) Drop the temporary table TEMP.
After the operation in .2.2, the segment, created as the temporary table TEMP is now a partition in the partitioned table, and the segment which was partition before 2.2 is now a normal, nonpartitioned tabel TEMP, so in this manner we have taken out of the offlined tablespace one of the partitions. The table TEMP we can safely drop, since it is not partitioned and the fact that it resides in offlined tablespace does not prevents it from droping.

2.4) Repeat 2.1 to 2.3 for every partition (every row) returned by the query in 1)

3)  Now, you should not have any partitions in offline tablespaces. Drop the offlined tablespaces and the empty partitions from the partitioned tables.