【DB笔试面试438】如何正确的删除表空间数据文件？
【DB笔试面试438】如何正确的删除表空间数据文件？
=如何正确的删除表空间数据文件？
答案
（一）DROP DATAFILE
可以使用如下的命令删除一个表空间里的数据文件：
ALTER TABLESPACE TS_DD_LHR DROP DATAFILE n; --n为数据文件号
ALTER TABLESPACE TS_DD_LHR DROP DATAFILE '/tmp/ts_dd_lhr01.dbf';
关于该命令需要注意以下几点：
① 该语句会删除磁盘上的文件并更新控制文件和数据字典中的信息，删除之后的原数据文件序列号可以重用。
② 该语句只能是在相关数据文件ONLINE的时候才可以使用。如果说对应的数据文件已经是OFFLINE，那么仅针对字典管理表空间（Dictionary-Managed Tablespace，DMT）可用，而对于本地管理表空间（Locally Managed Tablespace，LMT）不能使用，否则会报错“ORA-03264: cannot drop offline datafile of locally managed tablespace”。如果数据文件是RECOVER状态，那么该命令依然不能使用。
③ 不能删除一个表空间中第一个添加的数据文件，否则会报错，形如“ORA-03263: cannot drop the first file of tablespace TS_DD_LHR”。
④ 若一个表空间只包含1个数据文件，则不能删除该数据文件，否则会报错，形如“ORA-03261: the tablespace TS_DD_LHR has only one file”。
⑤ 必须为空，否则会报：ORA-03262: the file is non-empty。值得注意的是，non-empty的含义是有EXTENT被分配给了TABLE，而不是该TABLE中有无ROWS，此时若是使用“DROP TABLE XXX;”是不行的，必须使用“DROP TABLE XXX PURGE;”或者在已经使用了“DROP TABLE XXX;”的情况下，再使用“PURGE TABLE "XXX表在回收站中的名称";”来删除回收站中的该表，否则空间还是不释放，数据文件仍然不能DROP。
⑥ 不能删除SYSTEM表空间的数据文件，否则报错“ORA-01541: system tablespace cannot be brought offline; shut down if necessary”。
需要注意的是，据官方文档介绍说，处于READ ONLY状态的表空间数据文件也不能删除，但经过实验证明，其实是可以删除的。
（二）OFFLINE和OFFLINE DROP的区别
与删除数据文件相似的还有如下的命令（其中的“'FILE_NAME'”也可以用文件号替代）：
ALTER DATABASE DATAFILE 'FILE_NAME' OFFLINE;
ALTER DATABASE DATAFILE 'FILE_NAME' OFFLINE FOR DROP;--FOR也可以省略
需要注意的是，该命令不会删除数据文件，只是将数据文件的状态更改为RECOVER。OFFLINE FOR DROP命令相当于把一个数据文件置于离线状态，并且需要恢复，并非删除数据文件。数据文件的相关信息还会存在数据字典和控制文件中。
对于归档模式而言，“OFFLINE FOR DROP”和“OFFLINE”没有什么区别，因为Oracle会忽略FOR DROP选项。因为OFFLINE之后还需要进行RECOVER才可以ONLINE。
对于非归档模式而言，只能执行“OFFLINE FOR DROP”。若不加FOR DROP选项，则会报错“ORA-01145: offline immediate disallowed unless media recovery enabled”。因为非归档模式没有归档文件来进行RECOVER操作。如果OFFLINE之后，速度足够快，联机Redo日志文件里的数据还没有被覆盖掉，那么在这种情况下，还是可以进行RECOVER操作的。
（三）OS级别删除了数据文件后的恢复
若使用了“ALTER DATABASE DATAFILE N OFFLINE DROP;”命令，则并不会删除数据文件，这个时候可以先ONLINE后再用“ALTER TABLESPACE XXX DROP DATAFILE N;”删除。如果执行“ALTER DATABASE DATAFILE N OFFLINE DROP;”后并在OS级别删除了数据文件，那么首先需要使用“ALTER DATABASE CREATE DATAFILE N AS '/tmp/ts_dd_lhr02.dbf'';”来添加一个数据文件，然后再执行RECOVER并ONLINE后再用“ALTER TABLESPACE XXX DROP DATAFILE N;”命令删除。如果产生的日志文件以及丢失，那么目标文件就不能再恢复了，这个时候只能删除表空间了，命令为：“DROP TABLESPACE XXX INCLUDING CONTENTS AND DATAFILES;”。
示例如下：
SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;
Tablespace created.
SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TS_DD_LHR has only one file
SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;
Tablespace altered.
SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TS_DD_LHR
SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
Tablespace altered.
SYS@ora10g> ! ls -l /tmp/ts_dd_lhr0*
-rw-r----- 1 oracle oinstall 10493952 Jun 29 14:58 /tmp/ts_dd_lhr01.dbf
--------------------------------------------------
SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;
Tablespace altered.
SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' offline drop;
Database altered.
SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf
-rw-r----- 1 oracle oinstall 10493952 Jun 29 15:17 /tmp/ts_dd_lhr02.dbf
SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;
alter database datafile '/tmp/ts_dd_lhr02.dbf' online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/tmp/ts_dd_lhr02.dbf'
SYS@ora10g> recover datafile 9;
Media recovery complete.
SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;
Database altered.
SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
Tablespace altered.
SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf
ls: cannot access /tmp/ts_dd_lhr02.dbf: No such file or directory
SYS@orclasm > create table t_ts_dd_lhr tablespace ts_dd_lhr as select * from dual;
Table created.
SYS@orclasm > truncate table t_ts_dd_lhr;
Table truncated.
SYS@orclasm >
SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
SYS@orclasm > drop table t_ts_dd_lhr;
Table dropped.
SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
SYS@orclasm > purge recyclebin;
Recyclebin purged.
SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
Tablespace altered.
SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;
alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;
Tablespace created.
SYS@ora10g>
Tablespace altered.
SYS@ora10g>
SYS@ora10g> alter tablespace ts_dd_lhr read only;
Tablespace altered.
SYS@ora10g> select * from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS LOGGING
------------------------------ --------- --------- ---------
SYSTEM ONLINE PERMANENT LOGGING
UNDOTBS1 ONLINE UNDO LOGGING
SYSAUX ONLINE PERMANENT LOGGING
TEMP ONLINE TEMPORARY NOLOGGING
USERS ONLINE PERMANENT LOGGING
EXAMPLE ONLINE PERMANENT NOLOGGING
TS10GTEST ONLINE PERMANENT LOGGING
HHRIS ONLINE PERMANENT LOGGING
TS_DD_LHR READ ONLY PERMANENT LOGGING
9 rows selected.
SYS@ora10g>
SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';
alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TS_DD_LHR
SYS@ora10g>
SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';
Tablespace altered.
SQL>select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
----------------- ---------------------------------------------------
SYSTEM +DATA/oraee/datafile/system.260.870970687
SYSAUX +DATA/oraee/datafile/sysaux.261.870970721
UNDOTBS1 +DATA/oraee/datafile/undotbs1.262.870970751
UNDOTBS2 +DATA/oraee/datafile/undotbs2.264.870970801
USERS +DATA/oraee/datafile/users.265.870970831
EXIMTRX +DATA/oraee/datafile/eximtrx.270.871293623
EXIMUSER +DATA/oraee/datafile/eximuser.271.871293631
TS_EXIMTRX +DATA/oraee/datafile/ts_eximtrx.272.890144851
TS_EXIMTRX +DATA/oraee/datafile/ts_eximtrx.273.890146243
TS_EXIMUSER +DATA/oraee/datafile/ts_eximuser.274.892913553
TS_EXIMUSER /oracle/app/oracle/product/11.2.0/db/dbs/+DATAA 11g操作
SQL>alter tablespace TS_EXIMUSER drop datafile '/oracle/app/oracle/product/11.2.0/db/dbs/+DATAA'; ---->无法识别数据文件
SQL>alter database datafile 11 offline drop;
SQL> select file#,status,ts# from v$datafile;
FILE# STATUS TS#
---------- ------- ----------
1 SYSTEM 0
2 ONLINE 1
3 ONLINE 2
4 ONLINE 4
5 ONLINE 5
6 ONLINE 6
7 ONLINE 7
8 ONLINE 8
9 ONLINE 8
10 ONLINE 13
11 RECOVER 13 ---------->
SQL> recover datafile 11;
Media recovery complete.
SQL> select file#,status,ts# from v$datafile;
FILE# STATUS TS#
---------- ------- ----------
1 SYSTEM 0
2 ONLINE 1
3 ONLINE 2
4 ONLINE 4
5 ONLINE 5
6 ONLINE 6
7 ONLINE 7
8 ONLINE 8
9 ONLINE 8
10 ONLINE 13
11 OFFLINE 13
SQL> alter database datafile 11 online;
Database altered.
SQL> select file#,status,ts# from v$datafile;
FILE# STATUS TS#
---------- ------- ----------
1 SYSTEM 0
2 ONLINE 1
3 ONLINE 2
4 ONLINE 4
5 ONLINE 5
6 ONLINE 6
7 ONLINE 7
8 ONLINE 8
9 ONLINE 8
10 ONLINE 13
11 ONLINE 13 ----->数据文件必须在ONLINE状态
11 rows selected.
SQL> alter tablespace TS_EXIMUSER drop datafile 11;
Tablespace altered.
SQL> select file#,status,ts# from v$datafile;
FILE# STATUS TS#
---------- ------- ----------
1 SYSTEM 0
2 ONLINE 1
3 ONLINE 2
4 ONLINE 4
5 ONLINE 5
6 ONLINE 6
7 ONLINE 7
8 ONLINE 8
9 ONLINE 8
10 ONLINE 13
10 rows selected. & 说明：
有关删除表空间数据文件的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2124605/
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步