Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT table_owner, table_name, partition_name, tablespace_name FROM dba_tab_partitions WHERE (table_owner, table_name) IN ( SELECT table_owner, table_name FROM dba_tab_partitions X WHERE x.tablespace_name = UPPER ('TABLESPACE_NAME') AND EXISTS (SELECT * FROM dba_tab_partitions Y WHERE x.table_owner = y.table_owner AND x.table_name = y.table_name AND y.tablespace_name <> UPPER ( '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:
1 | ALTER TABLE USER.TABLE MOVE PARTITION PARTITION_NAME TABLESPACE NEW_TABLESPACE_NAME NOLOGGING; |
The corresponding tablespace can then be dropped succesfuly.
1 2 3 | SQL> DROP TABLESPACE TABLO_ALANI INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. |
