PROBLEM:

While dropping a tablespace, got error ORA-14404.

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

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

Below query can also be used

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).

Now drop the tablespace: