My Oracle Support Banner

How To Drop An Offlined Tablespace When Partitions From A Partitioned Table Resides In It (Doc ID 267125.1)

Last updated on NOVEMBER 07, 2023

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 8.0.3 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


Often the administrators makes common mistake, deleting the files, belonging to the tablespaces with old partitions at OS level, without dropping the appropriate tablespaces.

Once deleted, the only way to start-up the database is to take the coresponding tablespace offline.

From now on, if one try to drop the offlined tablespace, ORA-14404: partitioned table contains partitions in a different tablespace appears.

If in opposite, try to drop the partitioned table ORA-14117: partition resides in offlined tablespace appears.
So, seems no valid way to solve this situation exists.

However, the exchanging of partitions can be used to take the partitions out of the offlined tablespace and subsequently drop the offlined tablespace. Exchanging partitions is dictionary operation, which does not verifies if the partition resides in an on-line or off-line tablespace.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.