alter database datafile offline drop 與 alter tablespace drop datafile 區(qū)別

1. alter database datafile 'file_name' offline drop

       該命令不會刪除數(shù)據(jù)文件，只是將數(shù)據(jù)文件的狀態(tài)更改為recover。 offline drop命令相當于把一個數(shù)據(jù)文件至于離線狀態(tài)，并且需要恢復(fù)，并非刪除數(shù)據(jù)文件。 數(shù)據(jù)文件的相關(guān)信息還會存在數(shù)據(jù)字典和控制文件中。

1.1 對于歸檔模式：

       alter database datafile 'file_name' offline 和 offline drop 沒有什么區(qū)別。 因為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. 因為非歸檔模式?jīng)]有歸檔文件來進行recover操作，當然，如果offline 之后，速度足夠塊，online redo里的數(shù)據(jù)還沒有被覆蓋掉，那么這種情況下，還是可以進行recover的。

oracle 11g:

SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;

2. alter tablesapce tablespace_name drop datafile 'datafile_name'

       該語句會刪除控制文件和磁盤上的文件，刪除之后的原數(shù)據(jù)文件序列號可以重用。

       注意，該語句 只能是datafile online的時候 才可以使用。如果說對應(yīng)的數(shù)據(jù)文件已經(jīng)是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

--對應(yīng)的物理文件dave02.dbf 已經(jīng)被刪除了

我們將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

對應(yīng)的物理文件沒有了。

alter tablespace test drop datafile 8; 

命令不能drop 非空的數(shù)據(jù)文件， 如果要drop 某個數(shù)據(jù)文件，需要先把對象移除走，等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