体验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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/60857/viewspace-804115/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/60857/viewspace-804115/