If the tablespace that is dropped is empty, but is the default storage space for any partition, the drop operation will cause the ORA-14404 error and will not be dropped.
An error will occur during the process as follows.
SQL> drop tablespace TABLE_SPACE_NAME including contents ;
drop tablespace TABLE_SPACE_NAME including contents
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
In this case, it is necessary to detect the partition and change the tablespace information.
You can use the following query to detect the partition that uses Tablespace.
WHERE (table_owner, table_name) IN ( SELECT table_owner, table_name
FROM dba_tab_partitions X
WHERE x.tablespace_name =
FROM dba_tab_partitions Y
WHERE x.table_owner =
AND x.table_name =
AND y.tablespace_name <>
GROUP BY table_owner, table_name)
ORDER BY 1, 2, partition_position;
Write your tablespace name instead of TABLESPACE_NAME in the above query.
After detecting Partition, you can change the tablespace that partittion uses with the following command:
ALTER TABLE USER.TABLE MOVE PARTITION PARTITION_NAME TABLESPACE NEW_TABLESPACE_NAME NOLOGGING;
The corresponding tablespace can then be dropped succesfuly.
SQL> DROP TABLESPACE TABLO_ALANI INCLUDING CONTENTS AND DATAFILES;