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;
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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2149501/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- alter database drop datafile 與 drop tablespace file 的區別Database
- alter database datafile 4 offline drop;Database
- alter database datafile .... offline drop的問題Database
- alter database datafile offline and alter database tablespace ...offlineDatabase
- alter database datafile offline drop相關問題Database
- datafile offline 與alter tablespace offline 的區別
- ALTER DATABASE DATAFILE OFFLINEDatabase
- ALTER DATABASE 與 ALTER TABLESPACE OFFLINE的區別Database
- alter database offline 與 alter database offline drop效果比對Database
- alter database create datafileDatabase
- alter database create datafile '' as ''Database
- [20111227]Alter database datafile offline drop後的恢復.txtDatabase
- tablespace offline與datafile offline 區別
- alter database ... create datafile的原理及用途Database
- 恢復一則 alter database create datafile '' as ''Database
- tablespace offline 和datafile offline的區別
- Alter database datafile resize ORA-03297 原因解析Database
- UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statementDatabase
- v$lock之alter table drop column與alter table set unused column區別系列五
- oracle裡tablespace offline和datafile offline的區別Oracle
- 資料庫審計（create/alter/drop table、user、tablespace）資料庫
- alter system dump datafile headerHeader
- 在alter tablespace_datafile begin backup_offline_oracle block之fileq和ckptq變化OracleBloC
- alter database和alter system和alter session的區別DatabaseSession
- 由drop datafile導致的oracle bugOracle
- 資料檔案實驗操作datafile的create/offline/drop/rename等操作
- 重建控制檔案與 datafile offline,tablespace read only
- 忍不住問下alter system 和alter database的區別Database
- oracle 10g R2 drop empty datafileOracle 10g
- alter system events與alter system event的區別
- 體驗Oracle 10gR2的drop empty datafileOracle 10g
- VMS: ALTER TABLESPACE/DATABASE TO RENAME FILES on VMS [ID 9560.1]Database
- Display Storage Map for Database | Tablespace | Datafile Storage_1377458.1Database
- Drop DatabaseDatabase
- SQL truncate 、delete與drop區別SQLdelete
- 冷備手工完全恢復（recover database，recover tablespace，recover datafile）Database
- alter database in OracleDatabaseOracle
- truncate delete drop 區別delete