ORA-14407: partitioned table contains subpartitions in a different tablespace

Error: During dropping the tablespace, we are getting the following error but from dba_segments it is empty.

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

OR

SQL> drop tablespace_name including contents and datafiles;
drop tablespace_name including contents and datafiles
*
ERROR at line 1:
ORA-14407: partitioned table contains subpartitions in a different tablespace

Cause: Drop a tablespace cause the error because the table with a partition or subpartition is not completely contained in this tablespace.

SQL> select owner,segment_name,segment_type, partition_name,tablespace_name from dba_segments where tablespace_name='tablespace_name';
no row found;

Solution: Check the table which has a partition and look for the tablespace associated with any table partition. We can move the partition of the table to another tablespace or drop these tables if not needed.

1. Check the table and partition belong to the tablespace.

-- Check the tablespace 
SQL> execute sys.dbms_tts.transport_set_check('tablespace_name', true);
PL/SQL procedure successfully completed.

-- List of table got in following query after above package:
SQL> select * from sys.transport_set_violations;
VIOLATIONS
-----------------------------------------------------------------------------------
Default Partition (Table) Tablespace USERS for partition_name not contained in transportable set.
Partitioned table TABLE_NAME is partially contained in the transportable set.

Check the table with SQL Query also:

-- For partition 
select table_owner, table_name, partition_name,tablespace_name from dba_tab_partitions X where x.tablespace_name='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<>'tablespace_name');

--For Subpartition:

select table_owner, table_name, partition_name,subpartition_name,tablespace_name from dba_tab_subpartitions X where x.tablespace_name=upper('tablespace_name') and exists (select * from dba_tab_subpartitions Y where x.table_owner=y.table_owner and x.table_name=y.table_name and y.tablespace_name<>upper('tablespace_name'));

2. Move the table to other tablespace:

--Move Partition
ALTER TABLE t1 MOVE PARTITION p2022q1 ONLINE TABLESPACE users UPDATE INDEXES;

OR

--Move Subpartition
ALTER TABLE t1 MOVE SUBPARTITION subpart202201 ONLINE TABLESPACE users UPDATE INDEXES;

OR

ALTER TABLE t1 DROP PARTITION p2022q1;