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. |