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

?

1 2 3 4 5 6 7 8

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>

　　

?

1 2 3 4 5 6 7 8

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

　　

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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>

　　

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