| 10G删除空数据文件 | DBA之路：OoNiceDream |

10G删除空数据文件

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/05/10g_drop_datafile.html

Oracle 9I、10G都可以采用：Alter database datafile file_name offline drop;来删除数据文件，但当数据文件为非空时，存放在该数据文件上的数据就无法读据了：

SYS@sky> alter database datafile
  2  'E:\USERS03.DBF' offline drop;
 
数据库已更改。
 
SYS@sky> select count(*) from test01;
select count(*) from test01
                     *
ERROR 位于第 1 行:
ORA-00376: 此时无法读取文件 6
ORA-01110: 数据文件 6: 'E:\USERS03.DBF'

采用该命令来删除数据文件，相关数据字典信息是无法清除的：

SYS@sky> select FILE#,TS#,name,status from v$datafile
  2  where ts#=4;
 
FILE#  TS# NAME                              STATUS
----- ---- --------------------------------- -------
    2    4 E:\USERS02.DBF                    ONLINE
    4    4 E:\USERS01.DBF                    ONLINE
    6    4 E:\USERS03.DBF                    RECOVER

不过这条命令在恢复时还是很挺有用的，比如只恢复某个表空间的数据时，其它表空间的数据文件就可以offline drop了。

从Oracle 10G R2开始，可以采用：Alter tablespace tablespace_name drop datafile file_name;来删除一个空数据文件，并且相应的数据字典信息也会清除:

sys@ORCL>select 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
 
sys@ORCL>alter tablespace users add datafile
  2  '/u01/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend off;
 
Tablespace altered.
 
sys@ORCL>select 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
 
sys@ORCL>drop table test;
 
Table dropped.
 
sys@ORCL>create table test tablespace users
  2  as 
  3  select * from dba_objects;
 
Table created.
 
sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=9;
 
SEGMENT_NAME                      FILE_ID     BLOCKS
------------------------------ ---------- ----------
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9          8
TEST                                    9        128
TEST                                    9        128
 
17 rows selected.
 
sys@ORCL>alter table test move tablespace PERFSTAT;
 
Table altered.
 
sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=9;
 
no rows selected
 
sys@ORCL>alter tablespace users drop datafile
  2  '/u01/app/oracle/oradata/orcl/users02.dbf';
 
Tablespace altered.
 
sys@ORCL>select 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
 
sys@ORCL>

当然如果数据文件是非空的，drop时就有提示了：

sys@ORCL>alter tablespace users drop datafile
  2  '/u01/app/oracle/oradata/orcl/users02.dbf';
alter tablespace users drop datafile
*
ERROR at line 1:
ORA-03262: the file is non-empty