Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command
Last updated on MARCH 19, 2018
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 18.104.22.168.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Nov-2011***
***Checked for relevance on 27-Aug-2014***
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
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms