ORA-14404 error during the drop tablespace operation

dbtut

7 years ago

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

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.

  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;

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:

ALTER TABLE USER.TABLE MOVE PARTITION PARTITION_NAME TABLESPACE NEW_TABLESPACE_NAME NOLOGGING;

1	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;

Tablespace dropped.

SQL> DROP TABLESPACE TABLO_ALANI INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.