联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有些时候，想删除一个数据文件(临时文件),在10g之前的版本，要删除一个数据文件，必须删除该数据文件所属的表空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件，导致数据库不能正常启动;稍微等点数据库的朋友，会先offline数据文件，然后os级别删除，但是这条数据文件的记录还保留在数据字典中，不爽(特殊处理办法见:清除离线数据文件记录)。在10g及其以后版本中，oracle提供了alter tablespace talbespace_name drop datafile/tempfile path/file_id进行删除某个数据文件
一、删除数据文件
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 8 11:22:38 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> set line 120
SQL> select file_id,file_name,tablespace_name
2 from dba_data_files order by tablespace_name;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
9 /opt/oracle/oradata/test/odu01.dbf ODU
10 /opt/oracle/oradata/test/odu03.dbf ODU
8 /opt/oracle/oradata/test/odu02.dbf ODU
3 /opt/oracle/oradata/test/sysaux01.dbf SYSAUX
1 /opt/oracle/oradata/test/system01.dbf SYSTEM
2 /opt/oracle/oradata/test/undotbs01.dbf UNDOTBS1
5 /opt/oracle/oradata/test/user32g.dbf USERS
7 /opt/oracle/oradata/test/user02.dbf USERS
4 /opt/oracle/oradata/test/users01.dbf USERS
6 /opt/oracle/oradata/test/xifenfei01.dbf XFF
11 /opt/oracle/oradata/test/xifenfei03.dbf XFF
11 rows selected.
SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec 8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall 387981312 Dec 8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall 534781952 Dec 8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall 1289756672 Dec 8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 00:05 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 00:05 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 8 00:05 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall 20979712 Dec 8 00:05 /opt/oracle/oradata/test/xifenfei01.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 00:05 /opt/oracle/oradata/test/xifenfei03.dbf
SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty
SQL> col segment_name for a20
SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
2 where file_id=11;
OWNER SEGMENT_NAME FILE_ID BLOCKS
------------------------------ -------------------- ---------- ----------
CHF XFF_TEST 11 8
CHF XFF_TEST 11 128
CHF XFF_TEST 11 128
CHF T_XFF 11 128
CHF T_XFF 11 128
SQL> alter table chf.xff_test move tablespace users;
Table altered.
SQL> alter table chf.t_xff move tablespace users;
Table altered.
SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
2 where file_id=11;
no rows selected
SQL> alter tablespace xff drop datafile 11;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name
2 from dba_data_files order by tablespace_name;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
9 /opt/oracle/oradata/test/odu01.dbf ODU
10 /opt/oracle/oradata/test/odu03.dbf ODU
8 /opt/oracle/oradata/test/odu02.dbf ODU
3 /opt/oracle/oradata/test/sysaux01.dbf SYSAUX
1 /opt/oracle/oradata/test/system01.dbf SYSTEM
2 /opt/oracle/oradata/test/undotbs01.dbf UNDOTBS1
4 /opt/oracle/oradata/test/users01.dbf USERS
7 /opt/oracle/oradata/test/user02.dbf USERS
5 /opt/oracle/oradata/test/user32g.dbf USERS
6 /opt/oracle/oradata/test/xifenfei01.dbf XFF
10 rows selected.
SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec 8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall 387981312 Dec 8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall 534781952 Dec 8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall 1289756672 Dec 8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 12:52 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 12:52 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 8 12:52 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall 20979712 Dec 8 12:52 /opt/oracle/oradata/test/xifenfei01.dbf
二、删除临时表空间文件
SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/test/xff_temp02.dbf' size 10m;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name from dba_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
2 /opt/oracle/oradata/test/xff_temp02.dbf TEMP
1 /opt/oracle/oradata/test/temp01.dbf TEMP
SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec 8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall 387981312 Dec 8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall 534781952 Dec 8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall 1289756672 Dec 8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 13:00 /opt/oracle/oradata/test/xff_temp02.dbf
-rw-r----- 1 oracle oinstall 20979712 Dec 8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf
SQL> alter tablespace temp drop tempfile 2;
Tablespace altered.
SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec 8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall 387981312 Dec 8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall 534781952 Dec 8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall 1289756672 Dec 8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall 20979712 Dec 8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf
三、注意事项
1、Oracle Database 10g Release 2及其以上版本
2、删除数据文件从数据库和系统
3、数据文件必须为空
4、数据文件不是对应表空间的最后一个
5、不能删除只读表空间中数据文件
6、不能删除system中数据文件
7、不能删除本地管理的offline表空间数据文件