Recently I was performing database reorg operation on one of pre-prod environment with traditional way of table/lob movement followed by index rebuild. After moving all segments from current tablespace to new, I was ready to drop original one.

SQL> select count(0) from dba_segments where tablespace_name='MY_TABLESPACE';
  COUNT(0)
----------         
0

And it errored out!!!

SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES
*ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

As first step, I tried purging dba_recyclebin, but it didn’t help.

After searching on metalink, I found following note with similar issue: ORA-14404 or ORA-14407 When Trying to Drop a Tablespace (Doc ID 1674989.1)

which talks about two causes of this issue:

1. An attempt was made to drop a tablespace which contains tables whose partitions or subpartitions are not completely contained in this tablespace. 

OR

2. This can happen due to deferred segment creation introduced in 11.2.

First cause was matching my case:

I tried finding out tables whose partitions were spread across other partitions along with tablespace MY_TABLESPACE & moved to it to new tablespace as well:

SELECT    'alter table '|| table_owner|| '.'|| table_name|| ' move partition '|| partition_name|| '   tablespace MY_TABLESPACE_NEW;'  FROM (SELECT DISTINCT table_name, partition_name          FROM dba_tab_partitions         WHERE tablespace_name = 'MY_TABLESPACE') a,       dba_tab_partitions b WHERE b.tablespace_name <> 'MY_TABLESPACE'       AND a.table_name = b.table_name;

After moving all affected table partitions, I once again tried dropping tablespace: MY_TABLESPACE. It once again failed with same error: ORA-14404.
Then I found same issue with some of index partitions as well, for which I used following command:

select distinct 'alter index ' || index_owner || '.' || a.index_name || ' rebuild partition ' || a.partition_name || ' tablespace MY_TABLESPACE_NEW;'from ( select distinct index_name,partition_namefrom dba_ind_partitionswhere tablespace_name = 'MY_TABLESPACE' ) a, dba_ind_partitions bwhere b.tablespace_name <> 'MY_TABLESPACE' and a.index_name = b.index_name;

Once indexes were rebuild, I was able to drop tablespace MY_TABLESPACE without any issues 🙂

Note: You may also get ORA-14407 is some cases, wherein sub-partitions are the issue, for which you will use above commands just with sub-partitions views.

Just to elaborate seconds possible cause of this error: deferred segment creation:

The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION  set to TRUE.So in simple words, if you have set this parameter to value TRUE, particular table will be populated in dba_tables once created, but will not be populated in dba_segments unless we insert a row to it. So in the case of defered segments, you will not be able to drop tablespace.

Hope so u will find this post very useful:-)

Cheers

Regards,
Adityanath