前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >在Oracle中,如何正确的删除表空间数据文件?

在Oracle中,如何正确的删除表空间数据文件?

作者头像
AiDBA宝典
发布2023-04-26 12:04:35
6.9K0
发布2023-04-26 12:04:35
举报
文章被收录于专栏:小麦苗的DB宝专栏

DROP DATAFILE

可以使用如下的命令删除一个表空间里的数据文件:

代码语言:javascript
复制
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状态的表空间数据文件也不能删除,但经过实验证明,其实是可以删除的。

删除数据文件可以参考:https://www.xmmup.com/oraclesuoxiaobiaokongjiandefangfa.html

Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文档 ID 1050261.1)

OFFLINE和OFFLINE DROP的区别

与删除数据文件相似的还有如下的命令(其中的“'FILE_NAME'”也可以用文件号替代):

代码语言:javascript
复制
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;”。

10g示例如下:

代码语言:javascript
复制
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> SYS@ora10g> 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操作:

代码语言:javascript
复制
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.                                   

数据文件中含有数据的处理办法

如果数据文件中有数据,需要先迁移,重建索引。

代码语言:javascript
复制
1、检查是否有数据:SQL> select owner,segment_name,tablespace_name from dba_extents where file_id=6;
2、ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
3、index_rebuild
4、alter tablespace TT_TEST drop datafile 6;

另外,若不再需要数据,那么可以考虑删除相关的schema,请参考:https://www.xmmup.com/oraclesuoxiaobiaokongjiandefangfa.html

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-11-17,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DB宝 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • DROP DATAFILE
  • OFFLINE和OFFLINE DROP的区别
  • OS级别删除了数据文件后的恢复
  • 数据文件中含有数据的处理办法
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档