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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值