Oracle无法删除数据文件

发表于 | 分类于

Oracle无法删除数据文件

有些时候，想删除一个数据文件(临时文件),在10g之前的版本，要删除一个数据文件，必须删除该数据文件所属的表空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件，导致数据库不能正常启动;稍微等点数据库的朋友，会先offline数据文件，然后os级别删除，但是这条数据文件的记录还保留在数据字典中，。在10g及其以后版本中，oracle提供了alter tablespace talbespace_name drop datafile/tempfile path/file_id进行删除某个数据文件。

Cannot Drop Empty Datafile From Tablespace ORA-03262 in oracle 11g R2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
--删除数据文件
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

删除临时表空间文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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