Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (Doc ID 1050261.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.1.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***

Symptoms

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

ORA-60 <self-deadlock>


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

Changes

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

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms