1、alter database datafile offline drop
该命令不会真正删除数据文件,在归档模式下相当于alter database datafile offline ,该命令只是将数据文件至于recover的状态。
SQL> alter database datafile 4 offline drop;
SQL> select tablespace_name,file_id,online_status from dba_data_files;
TABLESPACE_NAME FILE_ID ONLINE_
------------------------------ ---------- -------
USERS 4 RECOVER
UNDOTBS1 3 ONLINE
SYSAUX 2 ONLINE
SYSTEM 1 SYSTEM
UNDOTBS2 5 ONLINE
TAS_01 6 ONLINE
--在ASM下依然存在
ASMCMD> ls
SYSAUX.257.888685509
SYSAUX.269.888688505
SYSTEM.256.888685503
SYSTEM.268.888688501
UNDOTBS1.258.888685513
UNDOTBS1.270.888688507
UNDOTBS2.267.888686293
UNDOTBS2.279.888689181
USERS.259.888685513
USERS.271.888688509
SQL> alter tablespace uses drop datafile 4;
alter tablespace dave drop datafile 4
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace uses
OFFline以后也报错,只能online以后才能删除
--将datafile 4 Online
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
2、alter tablespace drop datafile
该语句会删除控制文件和磁盘上的文件,删除之后的原数据文件序列号可以重用