alter database datafile offline drop 与 alter tablespace drop datafile 区别

1. alter database datafile 'file_name' offline drop        该命令不会删除数据文件，只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态，并且需要恢复，并非删除数据文件。 数据文件的相关信息还会存在数据字典和控制文件中。 1.1 对于归档模式：        alter database datafile 'file_name' offline 和 offline drop 没有什么区别。 因为offline 之后多需要进行recover 才可以online。 如： SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE; SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE; 1.2 对于非归档模式：        如果是非归档模式，只能是offline drop. 因为非归档模式没有归档文件来进行recover操作，当然，如果offline 之后，速度足够块，online redo里的数据还没有被覆盖掉，那么这种情况下，还是可以进行recover的。 oracle 11g: SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;   2. alter tablesapce tablespace_name drop datafile 'datafile_name'        该语句会删除控制文件和磁盘上的文件，删除之后的原数据文件序列号可以重用。        注意，该语句 只能是datafile online的时候 才可以使用。如果说对应的数据文件已经是offline for drop，那么仅针对 dictionary managed tablespaces 可用。 3. 示例 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE    10.2.0.1.0      Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/rac/datafile/system.256.746634087 +DATA/rac/datafile/undotbs1.258.746634089 +DATA/rac/datafile/sysaux.257.746634087 +DATA/rac/datafile/users.259.746634089 +DATA/rac/datafile/undotbs2.264.746634255 SQL> create tablespace dave datafile '+DATA/rac/datafile/dave01.dbf' size 10M; Tablespace created. SQL> alter tablespace dave add datafile '+DATA/rac/datafile/dave02.dbf' size 10M; Tablespace altered. SQL> select file#,status,name from v$datafile;      FILE# STATUS  NAME ---------- ------- -------------------------------------------------------------          1 SYSTEM  +DATA/rac/datafile/system.256.746634087          2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089          3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087          4 ONLINE  +DATA/rac/datafile/users.259.746634089          5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255          6 ONLINE +DATA/rac/datafile/dave01.dbf          7 ONLINE  +DATA/rac/datafile/dave02.dbf SQL> alter database datafile '+DATA/rac/datafile/dave01.dbf' offline; Database altered. SQL> set wrap off; SQL> select file#,status,name from v$datafile;      FILE# STATUS  NAME ---------- ------- -------------------------------------------------------------          1 SYSTEM  +DATA/rac/datafile/system.256.746634087          2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089          3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087          4 ONLINE  +DATA/rac/datafile/users.259.746634089          5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255          6 RECOVER +DATA/rac/datafile/dave01.dbf          7 ONLINE  +DATA/rac/datafile/dave02.dbf 7 rows selected. SQL> alter tablespace dave drop datafile 6; alter tablespace dave drop datafile 6 * ERROR at line 1: ORA-03263: cannot drop the first file of tablespace DAVE 这里报错了，只有online 的才能删除。 我们删除dave02.dbf 看看   SQL> alter tablespace dave drop datafile 7; Tablespace altered. -- 删除成功。 SQL> select file#,status,name from v$datafile;      FILE# STATUS  NAME ---------- ------- -------------------------------------------------------------          1 SYSTEM  +DATA/rac/datafile/system.256.746634087          2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089          3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087          4 ONLINE  +DATA/rac/datafile/users.259.746634089          5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255          6 RECOVER +DATA/rac/datafile/dave01.dbf 6 rows selected. --去ASM 里看下物理文件是否删除掉了： [oracle@rac1 ~]$ export ORACLE_SID=+ASM1 [oracle@rac1 ~]$ asmcmd ASMCMD> ls DATA/ FRA/ ASMCMD> cd DATA ASMCMD> ls DAVE/ DB_UNKNOWN/ RAC/ ASMCMD> cd RAC ASMCMD> ls CONTROLFILE/ DATAFILE/ TEMPFILE/ spfiledave.ora spfilerac.ora ASMCMD> cd DATAFILE ASMCMD> ls SYSAUX.257.746634087 SYSTEM.256.746634087 UNDOTBS1.258.746634089 UNDOTBS2.264.746634255 USERS.259.746634089 dave01.dbf --对应的物理文件dave02.dbf 已经被删除了   我们将datafile 6 online 看看： SQL> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '+DATA/rac/datafile/dave01.dbf' --提示需要recover。 这也就是需要归档文件的原因。 SQL> recover datafile 6; Media recovery complete. SQL> alter database datafile 6 online; Database altered. SQL> select file#,status,name from v$datafile;      FILE# STATUS  NAME ---------- ------- -------------------------------------------------------------          1 SYSTEM  +DATA/rac/datafile/system.256.746634087          2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089          3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087          4 ONLINE  +DATA/rac/datafile/users.259.746634089          5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255          6 ONLINE  +DATA/rac/datafile/dave01.dbf 6 rows selected. 最后把整个表空间dave drop 掉： SQL> drop tablespace dave including contents and datafiles; Tablespace dropped. SQL> select file#,status,name from v$datafile;      FILE# STATUS  NAME ---------- ------- -------------------------------------------------------------          1 SYSTEM  +DATA/rac/datafile/system.256.746634087          2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089          3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087          4 ONLINE  +DATA/rac/datafile/users.259.746634089          5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255 去ASM里看一下： ASMCMD> ls SYSAUX.257.746634087 SYSTEM.256.746634087 UNDOTBS1.258.746634089 UNDOTBS2.264.746634255 USERS.259.746634089   对应的物理文件没有了。 alter tablespace test drop datafile 8;  命令不能drop 非空的数据文件， 如果要drop 某个数据文件，需要先把对象移除走，等drop 完成后，在移回来。   SELECT owner ownr,          segment_name name,          segment_type TYPE,          extent_id exid,          file_id fiid,          block_id blid,          blocks blks     FROM dba_extents    WHERE file_id = 8 ORDER BY block_id; alter table temp move tablespace test;  https://blog.csdn.net/tianlesoftware/article/details/6305600