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 OCTOBER 03, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 to 11.2.0.4 [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.
***Checked for relevance on 21-Feb-2013***.


Goal

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.

Solution

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
Goal
Solution
References

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