Oracle删除数据文件

先看官文介绍

You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE
command to drop a single data file or temp file. The data file must be empty. (A data
file is considered to be empty when no extents remain allocated from it.) When you
drop a data file or temp file, references to the data file or temp file are removed from
the data dictionary and control files, and the physical file is deleted from the file
system or Oracle Automatic Storage Management (Oracle ASM) disk group.
The following example drops the data file identified by the alias example_df3.f in the
Oracle ASM disk group DGROUP1. The data file belongs to the example tablespace.
    ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';
The next example drops the temp file lmtemp02.dbf, which belongs to the lmtemp tablespace.
    ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
This is equivalent to the following statement:
    ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
Note: If there are sessions using a temp file, and you attempt to drop
the temp file, then an error is returned, and the temp file is not
dropped. In this case, the temp file is taken offline, and queries that
attempt to use the temp file will fail while the temp file is offline.
Restrictions for Dropping Data Files
The following are restrictions for dropping data files and temp files:
■ The database must be open.  --数据库必须打开
■ If a data file is not empty, it cannot be dropped.  --数据文件必须为空，如果不为空还想删除数据文件，只能删除表空间
If you must remove a data file that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the data file.
■ You cannot drop the first or only data file in a tablespace.  --不能删除表空间的第一个数据文件，或者当表空间只剩下一个数据文件时不能删除，只能删除表空间
Therefore, DROP DATAFILE cannot be used with a bigfile tablespace.  --因此大表空间是不能删除数据文件的
■ You cannot drop data files in a read-only tablespace that was migrated from dictionary managed to locally managed. Dropping a data file from all other read-only tablespaces is supported.
■ You cannot drop data files in the SYSTEM tablespace.  --不能删除system表空间的数据文件
■ If a data file in a locally managed tablespace is offline, it cannot be dropped.  --如果是本地管理并且是offline的表空间，不能被删除

删除数据文件分为以下情况：

1、从操作系统删除（失误情况下）

     未从数据库中drop，但是手动从操作系统rm了数据文件

    （1）、如果数据文件还被需要，分两种情况

                1、如果有备份，可以从备份中恢复；

                2、如果没有备份，且删除窗口还未关闭，可以参考这篇文章（https://www.cnblogs.com/myheart-new/p/11936656.html）进行恢复数据文件（此时的数据文件是不一致的），此处又有以下两种情况；

                    启用了归档，可以做recover datafile xx;

                    未启用归档，要及时停止业务，避免redo信息被覆盖；如果redo被覆盖，我就不知道咋搞了。

                    如果没有备份，且删除窗口被关闭了，不好意思，我也不知道咋搞了；

    （2）、如果数据文件不被需要，确认上边的数据都不被需要的话，两种处理方式：

               1、将删除的数据文件置为offline，这样启动数据库就不会再去扫描

               2、删除整个表空间  drop tablespace xxx;

2、正常删除数据文件，要在数据库中执行语句；

     首先确认表空间数据数据无用；

     当数据文件为空，可以通过alter tablespace xxx drop datafile 'xxxxxxxx';删除

     当数据文件不为空且必须删除时，只能删除表空间；drop tablespace  xxxx;