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

今天因为文件系统满了，尝试将tempfile 移动到另一个文件系统，于是在给temporarily tablespace 加完新的tempfile 后，将原来的tempfile 删除，操作命令提示删除成功，alert 日志也显示执行成功，结果应用报如下错误：

ORA-00376: file 201 cannot be read at this time

后查数据库发现，原来的数据文件仍然存在于数据字典中，最后无奈，只好新建一个临时表空间解决问题，警察 MOS 发现是删除表空间数据文件时，无论是 datafile 还是 tempfile 一定要确保文件为空，切不能是tablespace 的第一个文件。

select a.file_name,a.tablespace_name,a.AUTOEXTENSIBLE,a.bytes/1024/1024,b.BIGFILE from dba_temp_files a, dba_tablespaces b where a.tablespace_name=b.tablespace_name;
SQL> SQL>
FILE_NAME                                                    TABLESPACE_NAME                AUT A.BYTES/1024/1024 BIG
------------------------------------------------------------ ------------------------------ --- ----------------- ---
/u01/database/oiddb/oradata/OIDPDB/temp01.dbf                TEMP                                                 NO
/u01/database/oiddb/oradata/OIDPDB/FA_iastemp.dbf            FA_IAS_TEMP                    NO                100 NO
/u01/database/oiddb/oradata/OIDPDB/iastemp.dbf               IAS_TEMP                       NO                100 NO
/u03/oradata/OIDPDB/temp02.dbf                               TEMP                           YES        246.992188 NO

SQL> set pagesize 999
SQL> set linesize 200
SQL> col FILE_NAME for a60
col tablespace_name for a30
select a.file_name,a.tablespace_name,a.AUTOEXTENSIBLE,a.bytes/1024/1024,b.BIGFILE from dba_temp_files a, dba_tablespaces b where a.tablespace_name=b.tablespace_name;
SQL> SQL>
FILE_NAME                                                    TABLESPACE_NAME                AUT A.BYTES/1024/1024 BIG
------------------------------------------------------------ ------------------------------ --- ----------------- ---
/u01/database/oiddb/oradata/OIDPDB/FA_iastemp.dbf            FA_IAS_TEMP                    NO                100 NO
/u01/database/oiddb/oradata/OIDPDB/iastemp.dbf               IAS_TEMP                       NO                100 NO
/u03/oradata/OIDPDB/temp022.dbf                              TEMP2                          YES              6144 NO

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

To drop a data file or temp file, it:

- Must be empty.
- Cannot be the first file that was created in the tablespace.
    In such cases, drop the tablespace instead.
- Cannot be in a read-only tablespace.
- Cannot be offline.

Solution

The missing datafile has left this tablespace in an indeterminate status. The tablespace itself can actually still be used. But when an object located in the missing datafile is accessed, the statement will error-out with:

ORA-00376: file 201 cannot be read at this time

The tablespace will need to be dropped as well. The 10gR2 feature to drop a single datafile from a tablespace cannot be use in this situation..

The following notes can be used to recover the data:
Note 216683.1: How to Recover Data from a Tablespace When One or Several Datafiles are Lost.
Note 286355.1: How to Recover OFFLINE Dropped Datafile in ARCHIVELOG MODE