體驗Oracle 10gR2的drop empty datafile

1.首先建立一個測試表空間,並新增資料檔案:

SQL> create tablespace test datafile 'd:\oracle\t01.dbf' size 1M;

Tablespace created.

SQL> alter tablespace test add datafile 'd:\oracle\t02.dbf' size 1M;

Tablespace altered.

SQL> select file#,name from v$datafile;

FILE# NAME
---------- ----------------------------------------
1 D:\ORACLE\ORADATA\XUE10G\SYSTEM01.DBF
2 D:\ORACLE\ORADATA\XUE10G\UNDOTBS01.DBF
3 D:\ORACLE\ORADATA\XUE10G\SYSAUX01.DBF
4 D:\ORACLE\ORADATA\XUE10G\USERS01.DBF
5 D:\ORACLE\T01.DBF
6 D:\ORACLE\T02.DBF

[@more@]

2.建立一張測試表,新增少量資料使資料不會分佈到新新增的資料檔案中:

SQL> create table t tablespace test as select * from dba_objects where rownum<5;

Table created.

SQL> select file_id from dba_extents where segment_name='T';

FILE_ID
----------
5

3.透過10gR2中新增語法alter tablespace ts drop datafile來刪除表空間中未使用的資料檔案.可以看到刪除單個資料會移除資料字典裡面該資料檔案的相關資訊和作業系統的物理檔案.

SQL> alter tablespace test drop datafile 'd:\oracle\t02.dbf';

Tablespace altered.

SQL> select tablespace_name,file_name name from dba_data_files;

TABLESPACE_NAME NAME
------------------------------ ---------------------------------------
SYSTEM D:\ORACLE\ORADATA\XUE10G\SYSTEM01.DBF
UNDOTBS1 D:\ORACLE\ORADATA\XUE10G\UNDOTBS01.DBF
SYSAUX D:\ORACLE\ORADATA\XUE10G\SYSAUX01.DBF
USERS D:\ORACLE\ORADATA\XUE10G\USERS01.DBF
TEST D:\ORACLE\T01.DBF

SQL> host dir d:\oracle\*.dbf
驅動器 D 中的卷是 DATA
卷的序列號是 3C3F-0528

d:\oracle 的目錄

2005-08-09 12:41 1,056,768 T01.DBF
1 個檔案 1,056,768 位元組
0 個目錄 1,398,095,872 可用字

4.如果資料檔案中有儲存資料的話，那麼該資料檔案是無法單獨刪除的：

SQL> alter tablespace test add datafile 'd:\oracle\t03.dbf' size 1M;

Tablespace altered.

SQL> select tablespace_name,file_id,file_name name from dba_data_files;

TABLESPACE FILE_ID NAME
---------- ---------- ----------------------------------------
SYSTEM 1 D:\ORACLE\ORADATA\XUE10G\SYSTEM01.DBF
UNDOTBS1 2 D:\ORACLE\ORADATA\XUE10G\UNDOTBS01.DBF
SYSAUX 3 D:\ORACLE\ORADATA\XUE10G\SYSAUX01.DBF
USERS 4 D:\ORACLE\ORADATA\XUE10G\USERS01.DBF
TEST 5 D:\ORACLE\T01.DBF
TEST 6 D:\ORACLE\T03.DBF


SQL> insert into t select * from dba_objects where rownum<1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> select distinct file_id from dba_extents where segment_name='T';

FILE_ID
----------
6
5

SQL> alter tablespace test drop datafile 'd:\oracle\t03.dbf';
alter tablespace test drop datafile 'd:\oracle\t03.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

5.日常測試恢復丟失單個資料檔案時，會為無法移除資料檔案在資料字典中而頭疼.不過10g R2中如果丟失資料檔案的話，資料字典裡面的資訊還是無法刪除的，不管裡面是否有無資料.只有對該資料檔案進行恢復並聯機後,才能對單個資料檔案進行刪除的.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del d:\oracle\t03.dbf

SQL> startup
ORACLE instance started.

Total System Global Area 83886080 bytes
Fixed Size 1246396 bytes
Variable Size 62917444 bytes
Database Buffers 16777216 bytes
Redo Buffers 2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\T03.DBF'

SQL> alter database datafile 6 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,status from v$datafile;

NAME STATUS
---------------------------------------- -------
D:\ORACLE\ORADATA\XUE10G\SYSTEM01.DBF SYSTEM
D:\ORACLE\ORADATA\XUE10G\UNDOTBS01.DBF ONLINE
D:\ORACLE\ORADATA\XUE10G\SYSAUX01.DBF ONLINE
D:\ORACLE\ORADATA\XUE10G\USERS01.DBF ONLINE
D:\ORACLE\T01.DBF ONLINE
D:\ORACLE\T03.DBF OFFLINE

6 rows selected.

SQL> alter tablespace test drop datafile 'd:\oracle\t03.dbf';
alter tablespace test drop datafile 'd:\oracle\t03.dbf'
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace

SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'D:\ORACLE\T03.DBF'

SQL> alter database create datafile 'd:\oracle\t03.dbf' as 'd:\oracle\t03.dbf';

Database altered.

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: 'D:\ORACLE\T03.DBF'

SQL> recover datafile 6 ;
Media recovery complete.
SQL> alter database datafile 6 online;

Database altered.

SQL> select count(*) from t;

COUNT(*)
----------
2101

SQL> truncate table t;

Table truncated.

SQL> alter tablespace test drop datafile 6;

Tablespace altered.

SQL> alter tablespace test add datafile 'd:\oracle\t04.dbf' size 1M;

Tablespace altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del d:\oracle\t04.dbf

SQL> startup
ORACLE instance started.

Total System Global Area 83886080 bytes
Fixed Size 1246396 bytes
Variable Size 62917444 bytes
Database Buffers 16777216 bytes
Redo Buffers 2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\T04.DBF'

SQL> alter database datafile 6 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter tablespace test drop datafile 6;
alter tablespace test drop datafile 6
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace

SQL> alter database create datafile 6 as 'd:\oracle\t04.dbf';

Database altered.

SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;

Database altered.

SQL> alter tablespace test drop datafile 6;

Tablespace altered.