Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command
(Doc ID 1050261.1)
Last updated on DECEMBER 15, 2019
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 22.214.171.124.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Attempting to drop a datafile from a tablespace using the 10.2 and higher feature:
alter tablespace ... drop datafile ... ;
fails. The errors reported may differ depending on the actual situation.
ORA-3262: the file is non-empty
ORA-3263: cannot drop the first file of tablespace <tablespacename>
ORA-3264: cannot drop offline datafile of locally managed tablespace
Some errors are rather straightforward. However some errors are not, like the ORA-60. While the answer to the most common errors are self explanatory, this note focuses on the ORA-60 self-deadlock error received..
Datafile was lost at the Operating System level, causing the datafile to be in an OFFLINE status. Because the datafile contained some temporary segments and extents, these became invalid or stray. This causes an ORA-60 when trying to drop the datafile from the tablespace.
The datafile may be listed as MISSING in the datafile name, the reason for this is explained in:
<Note 1050268.1>: Explanation of MISSING keyword in datafile name
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