alter database datafile offline drop 與 alter tablespace drop datafile 區別

http://blog.csdn.net/tianlesoftware/article/details/6305600










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 DROP;

1.2 對於非歸檔模式：

   如果是非歸檔模式，只能是offline drop. 因為非歸檔模式沒有歸檔檔案來進行recover操作，當然，如果offline 之後，速度足夠塊，online redo裡的資料還沒有被覆蓋掉，那麼這種情況下，還是可以進行recover的。

OFFLINE DROP:該命令不會刪除資料檔案，只是將資料檔案的狀態更改為recover。 offline drop命令相當於把一個資料檔案至於離線狀態，並且需要恢復，並非刪除資料檔案。 資料檔案的相關資訊還會存在資料字典和控制檔案中.
 

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 table_name move tablespace tablespace_name;