ORCLE删除数据库文件

在实际操作中很少会去删除数据库文件，但是凡事都有例外，由于一些特殊原因，例如存储方式变化、磁盘空间不够等，需要调整和删除一些无效的数据库文件，本文介绍一下实践出来的一种删除数据库文件的操作方式。
删除前请对数据进行备份，以防出现意外。

一、操作环境

数据库版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

操作系统：Windows Server 12

二、操作步骤

1、查询数据库文件

查询数据库包含的数据库文件，获取数据库文件的id

select file#,name from v$datafile;

在这里插入图片描述
这里的FILE#即为数据库文件的id

2、新建表空间

新建一个表空间并使用新的数据库文件，主要是用来转移原数据库文件上的存储内容

create tablespace mytablespace datafile 'c:\mytablespace.dbf' size 100m aotuextend ON next 10m;

创建的表空间为mytablespace ，下面的语句注意替换为自己新建的表空间名字

3、查询文件包含内容

查询要删除的数据库文件是否包含有内容，如果有的话需要转移到其他表空间上

--需移动的表数据
select DISTINCT 'alter table '|| owner||'.'||segment_name || ' move tablespace mytablespace;' from dba_extents where segment_type='TABLE' and file_id=13;
--需移动的索引数据
select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild tablespace mytablespace;' from dba_extents where segment_type='INDEX' and file_id=13;
--需移动的分区表数据
select DISTINCT 'alter table '|| owner||'.'|| segment_name || ' move partition '|| partition_name || ' tablespace mytablespace;' from dba_extents where segment_type='TABLE PARTITION' and file_id=13;
--需移动的分区表索引数据
select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild partition '|| partition_name || ' tablespace mytablespace;' from dba_extents where segment_type='INDEX PARTITION' and file_id=13;

已上SQL查询后会显示出来该数据文件包含的内容（只查询了表和索引情况），第一列的内容可以直接拷贝出来执行，执行后会把当前数据库文件存储的内容转移到新的表空间上。

4、删除数据库文件

ALTER TABLESPACE "USERS" DROP DATAFILE 'C:\XXX\USERS_170312.DBF';
ALTER TABLESPACE USERS DROP DATAFILE 12;

已上SQL选择一个执行即可，少做等待，等ORACLE删除文件。

5、物理删除文件

将要删除的物理文件，从磁盘上删除。建议先移动到一个临时位置，过一段时间在删除。

三、出现的问题

1、重建索引

表和索引移动后，索引会失效，需要对索引进行重建，建议在业务空闲时进行移动操作。
查询失效索引：

SELECT owner,
       table_name,
       index_name,
       status， 'alter INDEX ' || index_NAME || '  REBUILD ONLINE tablespace mytablespace;'
  FROM dba_indexes
 WHERE STATUS != 'VALID'
       AND OWNER = 'XXX';