实验课题：如何正确删除表空间数据文件？
1、DROP
DATAFILE
使用如下命令删除数据文件：
ALTER
TABLESPACE TEST DROP DATAFILE n; (说明：n为数据文件号)
ALTER
TABLESPACE TEST DROP DATAFILE '/u01/app/oracle/oradata/leodb/test02.dbf ';
注意以下：
a、该语句会删除磁盘上的文件并更新控制文件和数据字典中信息，删除后的原数据文件序列号可重用.
b、该语句只能在相关数据文件ONLINE时才可使用.如果相关数据文件为OFFLINE，那么仅针对字典管理表空间（Dictionary-Managed
Tablespace，DMT）可用，对于本地管理表空间（Locally
Managed Tablespace，LMT）不可用，否则报错“ORA-03264:
cannot drop offline datafile of locally managed tablespace”.如果数据文件是RECOVER状态，该命令也不可用.
c、不能删除表空间中第一个添加的数据文件，否则报错“ORA-03263:
cannot drop the first file of tablespace TEST”.
d、若表空间只包含1个数据文件，则不能删除该数据文件，否则报错“ORA-03261:
the tablespace TEST has only one file”.
e、所要删除的数据文件需为空，否则报错“ORA-03262:
the file is non-empty”，值得注意的是，non-empty的含义是有EXTENT被分配给了TABLE，而不是该TABLE中有无ROWS，此时若使用“DROP
TABLE XXX;”是不行的，必须使用“DROP TABLE XXX PURGE;”或者在已经使用了“DROP
TABLE XXX;”的情况下，再使用“PURGE TABLE "XXX表在回收站中的名称";”来删除回收站中的该表，否则空间还是不释放，数据文件仍然不能DROP.——此注意事项在11g中测试truncate后，数据文件能drop成功.
f、不能删除SYSTEM表空间的数据文件，否则报错“ORA-01541:
system tablespace cannot be brought offline; shut down if necessary”.
g、官方文档介绍处于READ ONLY状态的表空间数据文件不能删除，但经实验证明，是可以删除的.
2、OFFLINE和OFFLINE
DROP的区别
与删除数据文件相似的还有如下命令（其中“'FILE_NAME'”可用文件号替代）
ALTER
DATABASE DATAFILE 'FILE_NAME' OFFLINE;
ALTER
DATABASE DATAFILE 'FILE_NAME' OFFLINE FOR DROP;
(说明：FOR可省略)
注意以下：
a、该命令不会删除数据文件，只是将数据文件的状态更改为RECOVER.
b、OFFLINE
FOR DROP命令相当于把一个数据文件置于离线状态，并且需要恢复，并非删除数据文件.数据文件的相关信息还是会存在数据字典和控制文件中.
c、对于归档模式，“OFFLINE
FOR DROP”和“OFFLINE”没有区别，Oracle会忽略FOR
DROP选项.OFFLINE后需要进行RECOVER才可以ONLINE.
d、对于非归档模式，只能执行“OFFLINE
FOR DROP”.若不加FOR DROP选项，则会报错“ORA-01145:
offline immediate disallowed unless media recovery enabled”.因为非归档模式没有归档文件来进行RECOVER，如果OFFLINE后，速度足够快，联机Redo日志文件里的数据还没有被覆盖掉，那么在这种情况下，还是可以进行RECOVER操作的.
3、OS级别删除了数据文件后的恢复
若使用了“ALTER
DATABASE DATAFILE N OFFLINE DROP;”命令，并不会删除数据文件，此时可先ONLINE，再用“ALTER
TABLESPACE XXX DROP DATAFILE N;”删除.
注意：如果执行“ALTER DATABASE DATAFILE N OFFLINE
DROP;”后并在OS级别删除了数据文件，那么需先使用“ALTER DATABASE CREATE DATAFILE N
AS '/u01/app/oracle/oradata/leodb/test02.dbf';”来添加一个数据文件，然后再执行RECOVER并ONLINE后再用“ALTER
TABLESPACE XXX DROP DATAFILE N;”命令删除.
如果产生的日志文件已经丢失，那么目标文件就不能再恢复，此时只能删除表空间，命令为：“DROP TABLESPACE XXX INCLUDING CONTENTS AND DATAFILES;”.
实际操作：
数据库：oracle
11.2.0.4
系统：centos
7.9
环境：单实例
SQL> create tablespace test datafile '/u01/app/oracle/oradata/leodb/test01.dbf' size 10m;
Tablespace created.
测试表空间中只有一个数据文件，删除该文件报错ORA-03261.
SQL>
alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test01.dbf';
alter
tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test01.dbf'
*
ERROR at
line 1:
ORA-03261:
the tablespace TEST has only one file
SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/leodb/test02.dbf' size 10m;
Tablespace altered.
测试删除表空间第一个数据文件报错ORA-03263.
SQL>
alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test01.dbf';
alter
tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test01.dbf'
*
ERROR at
line 1:
ORA-03263: cannot drop the first file of tablespace TEST
SQL> alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test02.dbf';
Tablespace
altered.
SQL>
host ls -ltr /u01/app/oracle/oradata/leodb/test02.dbf
ls: cannot access /u01/app/oracle/oradata/leodb/test02.dbf: No such file or directory
SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/leodb/test02.dbf' size 10m;
Tablespace altered.
测试OFFLINE的数据文件需recover并online后才能drop
SQL> alter database datafile '/u01/app/oracle/oradata/leodb/test02.dbf' offline drop;
Database altered.
SQL>
host ls -ltr /u01/app/oracle/oradata/leodb/test02.dbf
-rw-r----- 1 oracle asmadmin 10493952 Aug 7 15:39 /u01/app/oracle/oradata/leodb/test02.dbf
SQL>
alter tablespace test drop datafile
'/u01/app/oracle/oradata/leodb/test02.dbf';
alter
tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test02.dbf'
*
ERROR at
line 1:
ORA-03264:
cannot drop offline datafile of locally managed tablespace
SQL>
alter database datafile '/u01/app/oracle/oradata/leodb/test02.dbf' online;
alter
database datafile '/u01/app/oracle/oradata/leodb/test02.dbf' online
*
ERROR at
line 1:
ORA-01113:
file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/leodb/test02.dbf'
SQL>
recover datafile 6;
Media
recovery complete.
SQL> alter database datafile '/u01/app/oracle/oradata/leodb/test02.dbf' online;
Database altered.
SQL> alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test02.dbf';
Tablespace altered.
SQL>
host ls -ltr /u01/app/oracle/oradata/leodb/test02.dbf
ls: cannot access /u01/app/oracle/oradata/leodb/test02.dbf: No such file or directory
测试non-empty的数据文件不能被drop.
SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/leodb/test02.dbf' size 10m autoextend on;
Tablespace
altered.
SQL> alter database datafile '/u01/app/oracle/oradata/leodb/test01.dbf' autoextend off;
Database altered.
SQL> create table t5 tablespace test as select * from dba_objects
Table
created.
SQL> insert into t5 select * from t5
690104
rows created.
SQL> commit;
Commit complete.
SQL> select bytes/1024/1024 from user_segments where segment_name='T5';
BYTES/1024/1024
---------------
160
SQL>
alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test02.dbf';
alter
tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test02.dbf'
*
ERROR at
line 1:
ORA-03262: the file is non-empty
SQL> truncate table t5;
Table truncated.
SQL> alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test02.dbf';
Tablespace altered.
测试表空间为read only，数据文件可drop.
SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/leodb/test02.dbf' size 10m;
Tablespace altered.
SQL> alter tablespace test read only;
Tablespace
altered.
SQL> select tablespace_name,status,contents,logging from dba_tablespaces;
TABLESPACE_NAME
STATUS CONTENTS LOGGING
---------------
--------- --------- ---------
SYSTEM ONLINE PERMANENT LOGGING
SYSAUX ONLINE PERMANENT LOGGING
UNDOTBS1 ONLINE UNDO
LOGGING
TEMP ONLINE TEMPORARY NOLOGGING
USERS ONLINE PERMANENT LOGGING
TEST READ ONLY PERMANENT LOGGING
6 rows
selected.
SQL> alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test02.dbf';
Tablespace altered.
SQL>
alter tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test01.dbf';
alter
tablespace test drop datafile '/u01/app/oracle/oradata/leodb/test01.dbf'
*
ERROR at
line 1:
ORA-03261: the tablespace TEST has only one file
参考网址：
https://blog.51cto.com/lhrbest/3296239
