Mir Sayeed Hassan – Oracle Blog

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

Got the below error, While dropping a tablespace.

SQL> drop tablespace TEST1_TBL including contents and datafiles;

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

Check any contains are available in tablespace

SQL> select count(*) from dba_segments where tablespace_name='TEST1_TBL';

COUNT(*)
----------
0

Note: As we could see., the above tablespace we don’t have any tables or data associated in it., therefore its empty.

Detecting any of tables, Indexes, partitions or subpartitions were spread across other partitions along with this tablespace., therefore try to move it to new tablespace or the one which are having any dependency.

Issue the below query to find:

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 ('TEST1_TBL')
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 (
'TEST1_TBL'))
GROUP BY table_owner, table_name) ORDER BY partition_position;

If you find the given tablespace are having a dependency on another tablespace., therefore it was not allowing me to drop the tablespace.

Output of the above query:

alter index TEST1_TBL.TEST1_INX1_ID rebuild partition TEST1_INX1_ID tablespace TEST1_TBL;

Solution:

As per the above result., Index need to be rebuild into new tablespace or new tablespace.

SELECT 'alter index TEST1_TBL.TEST1_INX1_ID rebuild partition ' || partition_name || ' tablespace TEST1_TBL;' from dba_ind_partitions where index_name='TEST1_INX1_ID' and index_owner='TEST1_TBL';

Apply on database:

SQL> alter index TEST1_TBL.TEST1_INX1_ID rebuild partition TEST1_INX1_ID tablespace TEST1_TBL;
Index TEST1_TBL.TEST1_INX1_ID altered.

Hence., you can successfully Drop the tablespace:

sql> drop tablespace TEST1_TBL including contents and datafiles;
Tablespace dropped.

Tablespace contain the Partition dependency:

SQL> drop tablespace  TEST2_TBL including contents and datafiles;

drop tablespace  TEST2_TBL including contents and datafiles;

*ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

Issue the below query.

SELECT 'alter table TEST2_TBL .' || table_name || ' move partition ' || partition_name || ' tablespace TEST2_TBL;' from dba_tab_partitions where tablespace_name in ('TEST2_TBL');

As per the above result., Move Partition from this tablespace to the dependency tablespace or new tablespace.

Output of the above query:

alter table TEST2_TBL.MESSAGE_XML move partition P440 tablespace TEST2_TBL;

Apply on database:

SQL> alter table TEST2_TBL.MESSAGE_XML move partition P440 tablespace TEST2_TBL ;
Table TEST2_TBL.MESSAGE_XML altered.

SQL> drop tablespace TEST2_TBL including contents and datafiles;
Tablespace TEST2_TBL dropped.

=====Hence tested & Verified in our test env=====