PROBLEM:

While dropping a tablespace, got error ORA-14404.

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

CAUSE & SOLUTION:

This error occurs when we try to drop a tablespace, which contains tables, whose partitions/subpartitions are not completely residing in this tablespace.

So let’s find the table name, which is causing this error.

1. Run transportable tablespace check against the tablespace

SQL> execute sys.dbms_tts.transport_set_check('TEST_2', true);

PL/SQL procedure successfully completed.

 SQL> select * from sys.transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
ORA-39901: Partitioned table DBA_RAJ.DBACLASS is partially contained in the transportable set.
ORA-39921: Default Partition (Table) Tablespace USERS for DBACLASS not contained in transportable set.

It shows partitioned table DBA_RAJ.DBACLASS is partially contained in this tablespace.

Below query can also be used

SET LINES 200
SET PAGESIZE 200
COL TABLE_NAME FOR A14
COL TABLE_OWNER FOR A14
COL PARTITION_NAME FOR A14
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='&TABLESPACE_TO_DROP' 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 NOT IN ('&TABLESPACE_TO_DROP')
group by table_owner, table_name)
order by 1,2,partition_position;


TABLE_OWNER    TABLE_NAME     PARTITION_NAME TABLESPACE_NAME
-------------- -------------- -------------- ------------------------------
DBA_RAJ        DBACLASS       DBACLASS_1     TEST
DBA_RAJ        DBACLASS       DBACLASS_2     TEST
DBA_RAJ        DBACLASS       DBACLASS_3     TEST
DBA_RAJ        DBACLASS       DBACLASS_4     TEST
DBA_RAJ        DBACLASS       DBACLASS_5     TEST_2 ----- > 

Now it is clear, that the partitioned table DBACLASS is spread over 2 tablespaces TEST and TEST_2.

So the solution is to either move/drop the partition which exists in the tablespace(which we are trying to drop).

SQL> alter table DBA_RAJ.DBACLASS drop partition DBACLASS_5;

Table altered.

Now drop the tablespace:

SQL>  drop tablespace  TEST_2 including contents and datafiles;

Tablespace dropped.