在我們詳細介紹之前，我們必須說清楚一點：Oracle不提供如刪除表、檢視一樣刪除資料檔案的方法，資料檔案是表空間的一部分，所以不能“移走”表空間。

一、使用offline資料檔案的方法

非歸檔模式使用：alter database datafile '...' offline drop;

歸檔模式使用： alter database datafile '...' offline;

說明：

1) 以上命令只是將該資料檔案OFFLINE，而不是在資料庫中刪除資料檔案。該資料檔案的資訊在控制檔案種仍存在。查詢v$datafile，仍顯示該檔案。

2) 歸檔模式下offline和offline drop效果是一樣的

3) offline後，存在此datafile上的物件將不能訪問

4) noarchivelog模式下，只要online redo日誌沒有被重寫，可以對這個檔案recover後進行online操作

實際使用案例：

直接刪除資料檔案後無法進入系統的解決方案

正常情況下，刪除表空間的正確方法為：

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

如果沒有透過以上命令刪除而直接刪除了資料檔案，將導致資料庫無法開啟。

如果直接刪除了資料檔案

普通使用者登入時，則報錯：

ORA-01033: ORACLE initialization or shutdown in progress

sys使用者可以正常登入

但進行操作時（SELECT count(1) FROM user_tables），則會報錯：

ORA-01219: 資料庫未開啟: 僅允許在固定表/檢視中查詢

如果執行命令alter database open以開啟資料庫時，又報如下錯：

ORA-01157: 無法標識/鎖定資料檔案 12 - 請參閱 DBWR 跟蹤檔案

ORA-01110: 資料檔案 12: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSTEST001.DBF'

說明資料庫沒找到這個資料檔案

因為資料檔案在沒有被offline的情況下物理刪除了,導致oracle的資料不一致,因此啟動失敗.

透過以下方法即可解決

解決方法：

sqlplus as sysdba;

SQL> alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSTEST001.DBF' offline drop;

SQL> alter database open;

SQL> drop tablespace CTBASEDATA;

二、Oracle 10G R2開始，可以採用：Alter tablespace tablespace_name drop datafile file_name;來刪除一個空資料檔案，並且相應的資料字典資訊也會清除:

file_id,file_name,tablespace_name from dba_data_files

2 where tablespace_name='USERS';

FILE_ID FILE_NAME TABLESPACE_NAME

------- -------------------------------------------- ------------------

4 /u01/app/oracle/oradata/orcl/users01.dbf USERS

tablespace users add datafile

2 '/u01/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend off;

Tablespace altered.

file_id,file_name,tablespace_name from dba_data_files

2 where tablespace_name='USERS';

FILE_ID FILE_NAME TABLESPACE_NAME

------- -------------------------------------------- -------------------

4 /u01/app/oracle/oradata/orcl/users01.dbf USERS

9 /u01/app/oracle/oradata/orcl/users02.dbf USERS

table test;

Table dropped.

table test tablespace users

2 as

3 select * from dba_objects;

Table created.

SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

2 where file_id=9;

SEGMENT_NAME FILE_ID BLOCKS

------------------------------ ---------- ----------

TEST 9 8

TEST 9 128

17 rows selected.

table test move tablespace PERFSTAT; --把表移動到其它表空間

Table altered.

SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

2 where file_id=9;

no rows selected

tablespace users drop datafile

2 '/u01/app/oracle/oradata/orcl/users02.dbf';

Tablespace altered.

file_id,file_name,tablespace_name from dba_data_files

2 where tablespace_name='USERS';

FILE_ID FILE_NAME TABLESPACE_NAME

------- -------------------------------------------- ---------------------

4 /u01/app/oracle/oradata/orcl/users01.dbf USERS

三、oracle 10g可以刪除臨時表空間的檔案

alter database tempfile '/home/oracle/temp01.dbf' drop including datafiles;

ALTER DATABASE 與 ALTER TABLESPACE OFFLINE的區別

一．DataFile離線或聯機的兩種方法：

① ALTER DATABASE 語句修改單獨的DataFile

② ALTER TABLESPACE 語句修改所有的DataFile

1、在ARCHIVRLOG模式下的更改DataFile狀態

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

或者用檔案號來表示 :

ALTER DATABASE DATAFILE 5 ONLINE;

ALTER DATABASE DATAFILE 5 OFFLINE;

注：只有在ARCHIVELOG模式下才可使用ALTER DATABASE來更改DataFile

2、在NOARCHIVELOG模式下使DataFile離線

由於在NOARCHIVELOG模式下，資料檔案離線後會造成資料的遺失，所以只能使用ALTER DATABASE語句下帶有DATAFILE和OFFLINE DROP子句的選項將該DataFile直接取消，例如該DataFile只包含臨時段資料，並沒有備份時

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users3.dbf' OFFLINE DROP;

3、修改TableSpace中所有DataFile或TempFile的可用性

ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}

ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}

注：修改某TableSpace中的所有資料檔案，但是TableSpace本身的狀態不改變。

總結：

① ALTER TABLESPACE可以在資料庫裝載狀態時釋出，無需開啟

② 涉及到系統表空間、撤銷表空間、預設臨時表空間時，必須是未開啟的資料庫

③ ALTER DATABASE DATAFILE 語句中必須填入檔案全名

二．表空間與資料檔案離線的區別

1. ALTER TABLESPACE ... OFFLINE

Does a checkpoint on the datafiles

Takes the datafiles offline

表空間Offline時，資料檔案的SCN會被凍結，而且表空間的資料檔案offline/online時又會發生檔案檢查點，使單個資料檔案SCN和資料庫其他問題不一致。

表空間online時，Oracle會取得當前SCN，解凍offline檔案SCN，和當前SCN同步。

tablespace offline有幾種選項可供選擇normal, temporary,immediate, for recovery,而在datafile中則沒有這些選項。

2. ALTER DATABASE DATAFILE ... OFFLINE

單純的offline datafile，將不會觸發檔案檢查點，只有針對offline tablespace的時候才會觸發檔案檢查點，這也是為什麼online datafile需要media recovery而online tablespace不需要。

注：只有在ARCHIVELOG模式下才可使用ALTER DATABASE來更改DataFile

Oracle 刪除資料檔案

相關文章