爱站程序员基地[code]有些时候，想删除一个数据文件(临时文件),在10g之前的版本，要删除一个数据文件，必须删除该数据文件所属的表空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件，导致数据库不能正常启动;稍微等点数据库的朋友，会先offline数据文件，然后os级别删除，但是这条数据文件的记录还保留在数据字典中，不爽(特殊处理办法见:清除离线数据文件记录)。在10g及其以后版本中，oracle提供了alter tablespace talbespace_name drop datafile/tempfile path/file_id进行删除某个数据文件一、删除数据文件[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 8 11:22:38 2011Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> col tablespace_name for a20SQL> col file_name for a50SQL> set line 120SQL> select file_id,file_name,tablespace_name2 from dba_data_files order by tablespace_name;FILE_ID FILE_NAME TABLESPACE_NAME---------- -------------------------------------------------- --------------------9 /opt/oracle/oradata/test/odu01.dbf ODU10 /opt/oracle/oradata/test/odu03.dbf ODU8 /opt/oracle/oradata/test/odu02.dbf ODU3 /opt/oracle/oradata/test/sysaux01.dbf SYSAUX1 /opt/oracle/oradata/test/system01.dbf SYSTEM2 /opt/oracle/oradata/test/undotbs01.dbf UNDOTBS15 /opt/oracle/oradata/test/user32g.dbf USERS7 /opt/oracle/oradata/test/user02.dbf USERS4 /opt/oracle/oradata/test/users01.dbf USERS6 /opt/oracle/oradata/test/xifenfei01.dbf XFF11 /opt/oracle/oradata/test/xifenfei03.dbf XFF11 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.dbfSQL> alter tablespace xff drop datafile 11;alter tablespace xff drop datafile 11*ERROR at line 1:ORA-03262: the file is non-emptySQL> col segment_name for a20SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents2 where file_id=11;OWNER SEGMENT_NAME FILE_ID BLOCKS------------------------------ -------------------- ---------- ----------CHF XFF_TEST 11 8CHF XFF_TEST 11 128CHF XFF_TEST 11 128CHF T_XFF 11 128CHF T_XFF 11 128SQL> 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_extents2 where file_id=11;no rows selectedSQL> alter tablespace xff drop datafile 11;Tablespace altered.SQL> select file_id,file_name,tablespace_name2 from dba_data_files order by tablespace_name;FILE_ID FILE_NAME TABLESPACE_NAME---------- -------------------------------------------------- --------------------9 /opt/oracle/oradata/test/odu01.dbf ODU10 /opt/oracle/oradata/test/odu03.dbf ODU8 /opt/oracle/oradata/test/odu02.dbf ODU3 /opt/oracle/oradata/test/sysaux01.dbf SYSAUX1 /opt/oracle/oradata/test/system01.dbf SYSTEM2 /opt/oracle/oradata/test/undotbs01.dbf UNDOTBS14 /opt/oracle/oradata/test/users01.dbf USERS7 /opt/oracle/oradata/test/user02.dbf USERS5 /opt/oracle/oradata/test/user32g.dbf USERS6 /opt/oracle/oradata/test/xifenfei01.dbf XFF10 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 TEMP1 /opt/oracle/oradata/test/temp01.dbf TEMPSQL> !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.dbfSQL> 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表空间数据文件
