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

最新推荐文章于 2021-04-14 11:24:18 发布

转载最新推荐文章于 2021-04-14 11:24:18 发布 · 313 阅读

本文介绍了解决在尝试删除包含跨表空间分区表的表空间时遇到的ORA-14404错误的方法。通过查询数据库元数据找到跨越不同表空间的表及其分区，并提供了具体的SQL查询语句来定位问题。最后，指导如何删除这些表以成功完成表空间的删除。

SQL> drop tablespace nn_data including contents and datafiles;
drop tablespace nn_data including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace


SQL>

$ oerr ora 14404
14404, 00000, "partitioned table contains partitions in a different tablespace"
// *Cause: An attempt was made to drop a tablespace which contains tables
//         whose partitions are not completely contained in this tablespace
// *Action: find tables with partitions which span the tablespace being
//          dropped and some other tablespace(s). Drop these tables or move
//          partitions to a different tablespace
$

首先找到到底是哪张表跨越了不同表空间：

SQL> SELECT x.table_name,
  2         x.partition_name,
  3         x.tablespace_name tablespace1,
  4         y.tablespace_name tablespace2
  5    FROM dba_tab_partitions x, dba_tab_partitions y
  6   WHERE x.tablespace_name = 'NN_DATA'
  7     AND y.tablespace_name <> 'NN_DATA'
  8     AND x.table_name = y.table_name;

TABLE_NAME                     PARTITION_NAME                 TABLESPACE1                    TABLESPACE2
------------------------------ ------------------------------ ------------------------------ ------------------------------
SP_TEST                        P1                             NN_DATA                        NN_INDEX

SQL>

删除对应的表之后，再删除表空间即可。