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