ORACLE 缩减表空间大小（删除存有数据的数据文件文件或缩减数据文件大小）

网上搜了好多都是连同数据文件连同表空间一起删除，通过自己学习，整理出一个删除表空间单个数据文件的方法，仅供参考。在没有数据写入表空间的时候操作比较好。
**

第一种 发方法删除数据文件

**
第一部、先创建一个零时的表空间，用来存放要移出的对象。

--创建表空间
CREATE TABLESPACE TRW DATAFILE  '/oracledb/data/ora11ha/TRW01.dbf' SIZE 100 M 
       AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED；
--查看数据文件
SELECT T1.NAME 表空间,T2.NAME 数据文件
FROM V$TABLESPACE T1,V$DATAFILE T2
WHERE T1.TS# = T2.TS# AND T1.NAME LIKE 'TRW%';

第二部、查看要删除的数据文件内都有哪些数据库对象，主要是表，表索引可以先删除在重新创建。

利用以下命令先查看数据文件下有哪些对象，并记录先这些对象

SELECT DISTINCT a.SEGMENT_NAME 对象名,PARTITION_NAME 分区名,a.SEGMENT_TYPE 类型 ,
		a.TABLESPACE_NAME 表空间名 ,a.FILE_ID 文件ID ,b.NAME 文件名 
FROM DBA_EXTENTS a  JOIN V$DATAFILE b ON a.FILE_ID=b.FILE# 
WHERE b.NAME = '/oracledb/data/ora11ha/TRW02.dbf' ;

第三部、查出对象后，我们先吧所有对象先移到创建的临时表空间，表索引可以先删除在重新创建

--移动表到临时表空间 -- ！！根据你的表是否是分区表选择！！
ALTER TABLE MT_AS_MSG_TRW MOVE TABLESPACE TRWN;  /*修改普通表*/
ALTER TABLE MT_AS_MSG_TRW MOVE PARTITION MT_AS_MSG_TRW2 TABLESPACE TRWN; /*修改分区表的一个分区*/
--删除索引或移动索引到临时表空间 -- ！！二选一！！
DROP INDEX MT_AS_MSG_TRW_MSGID_INDEX; /*删除索引*/
ALTER INDEX MT_AS_MSG_TRW_MSGID_INDEX REBUILD TABLESPACE TRWN;  /*移动索引*/

然后再查看数据文件中是否还有对象

SELECT DISTINCT a.OWNER 用户名,a.SEGMENT_NAME 对象名,PARTITION_NAME 分区名,a.SEGMENT_TYPE 类型 ,
		a.TABLESPACE_NAME 表空间名 ,a.FILE_ID 文件ID ,b.NAME 文件名 
FROM DBA_EXTENTS a  JOIN V$DATAFILE b ON a.FILE_ID=b.FILE# 
WHERE b.NAME = '/oracledb/data/ora11ha/TRW02.dbf' ;

此时数据文件 TRW02.dbf 已被移空

第四部、删除数据文件

--删除数据文件
ALTER TABLESPACE "TRW" DROP DATAFILE '/oracledb/data/ora11ha/TRW02.dbf';
--查看数据文件是否还存在
SELECT T1.NAME 表空间,T2.NAME 数据文件
FROM V$TABLESPACE T1,V$DATAFILE T2
WHERE T1.TS# = T2.TS# AND T1.NAME LIKE 'TRW%';

可看到数据文件已删除

第五部、把所有对象从临时表空间移到回到TRW表空间

--核实对象
SELECT DISTINCT a.SEGMENT_NAME 对象名,PARTITION_NAME 分区名,a.SEGMENT_TYPE 类型 ,
		a.TABLESPACE_NAME 表空间名 ,a.FILE_ID 文件ID ,b.NAME 文件名 
FROM DBA_EXTENTS a  JOIN V$DATAFILE b ON a.FILE_ID=b.FILE# 
WHERE b.NAME = '/oracledb/data/ora11ha/TRWN01.dbf' ;

--把表从临时表空间移回到TRW -- ！！根据你的表是否是分区表选择 ！！
ALTER TABLE MT_AS_MSG_TRW MOVE TABLESPACE TRW;
ALTER TABLE MT_AS_MSG_TRW MOVE PARTITION MT_AS_MSG_TRW2 TABLESPACE TRW; /*修改分区表的一个分区*/
--重建索引把索引从临时表空间移回到TRW -- ！！二选一！！
CREATE INDEX MT_AS_MSG_TRW_MSGID_INDEX ON MT_AS_MSG_TRW(MSGID) TABLESPACE TRW  /*重建索引*/
ALTER INDEX MT_AS_MSG_TRW_MSGID_INDEX REBUILD TABLESPACE TRW;  /*移动索引*/

--核实对象
SELECT DISTINCT a.SEGMENT_NAME 对象名,PARTITION_NAME 分区名,a.SEGMENT_TYPE 类型 ,
		a.TABLESPACE_NAME 表空间名 ,a.FILE_ID 文件ID ,b.NAME 文件名 
FROM DBA_EXTENTS a  JOIN V$DATAFILE b ON a.FILE_ID=b.FILE# 
WHERE b.NAME LIKE '/oracledb/data/ora11ha/TRW' ;

可以看出所有对象都已保存到了TRW表空间的TRW01.bdf文件中

第五部、最后一步，删除创建的TRWN临时表空间；

--删除创建的TRWN临时表空间及数据文件
DROP TABLESPACE TRWN INCLUDING CONTENTS AND DATAFILES;
--查看数据文件是否还存在
SELECT T1.NAME 表空间,T2.NAME 数据文件
FROM V$TABLESPACE T1,V$DATAFILE T2
WHERE T1.TS# = T2.TS# AND T1.NAME LIKE 'TRW%';

表空间TRWN已被删除；

**

第二种 缩减数据文件大小

**

第一步、查看空闲较大的的数据文件

WITH E AS (SELECT FILE_ID,SUM(BYTES) REAL_USED_BYTES FROM DBA_EXTENTS WHERE OWNER = 'XJUMSXCPMS' GROUP BY FILE_ID)
		,F AS( SELECT file_id ,SUM(BYTES) FREE_BYTES FROM dba_free_space GROUP BY FILE_ID)
SELECT TABLESPACE_NAME 表空间名
			,FILE_NAME	数据文件名
			,DECODE(AUTOEXTENSIBLE, 'YES', ROUND(MAXBYTES/1073741824, 2),ROUND(USER_BYTES/1073741824, 2)) 最大扩展G  
			,ROUND(USER_BYTES/1073741824, 2) 占硬盘G
			,ROUND(REAL_USED_BYTES/1073741824, 2) 对象数据G
			,ROUND(FREE_BYTES/1073741824, 2)  空闲大小G
FROM dba_data_files D JOIN E ON D.FILE_ID = E.FILE_ID
		 LEFT JOIN F ON D.FILE_ID = F.FILE_ID 
ORDER BY 1,2

查看服务器上文件真是大小为51M。

可见数据文件TRW01.dbf使用量特别小；

第二部、我们把TRW01.dbf，从50M缩减到20M，缩减后的大小一定要大于对象数据所占的大小

ALTER DATABASE DATAFILE '/oracledb/data/ora11ha/TRW01.dbf' RESIZE 20M;

文件以缩小为20M空间。

修改数据文件最大大小不限制

ALTER DATABASE DATAFILE '/oracledb/data/ora11ha/TRW01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

缩减空间到此结束。

其他实用SQL https://blog.csdn.net/trw777/article/details/104483680