文章目录
前言一、降低表空间高水位 1.查看表空间和对应物理文件名2.清空回收站3.整理表空间4.生成RESIZE代码 二、在删除时进行释放 1.清空表数据并释放表空间2.迁移表数据 参考文档前言
在使用Oracle进行录入测试时，我们会制造许多测试数据，而使用删除后表空间的高水位依旧无法自动下调，导致出现表空间出现空白数据，影响表空间性能的同时也占用了过多的存储空间。
一、降低表空间高水位
1.查看表空间和对应物理文件名
代码如下：
select b.file_id 物理文件号,b.file_name 物理文件名,b.tablespace_name 表空间,b.bytes/1024/1024 大小M,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.file_name,b.bytes order by b.tablespace_name
根据利用率可以直观的判断有那些表空间可以进行释放。
2.清空回收站
代码如下：
-- 清除用户回收站purge recyclebin;-- 清除全库回收站purge dba_recyclebin;
清除删除过程中遗留的数据。
3.整理表空间
代码如下：
alter tablespace tablespace_name coalesce;
整合表空间的碎片增加表空间的连续性
4.生成RESIZE代码
代码如下：
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,ceil(HWM * a.block_size)/1024/1024 ResizeTo,(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,'alter database datafile '''||a.name||''' resize '||ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMDfrom v$datafile a,(select file_id,max(block_id+blocks-1) HWMfrom dba_extents where file_id in(select b.file# From v$tablespace a ,v$datafile bwhere a.ts#=b.ts# and a.name='tablespace_name')group by file_id) bwhere a.file# = b.file_id(+)and (a.bytes - HWM *block_size)>0order by 5
上述代码执行后，可以生成一个对应表空间RESIZE的SQL代码，此时运行对应的SQL代码即可。
二、在删除时进行释放
1.清空表数据并释放表空间
代码如下：
-- 清空表数据truncate table table_name;-- 释放表空间alter table table_name deallocate UNUSED KEEP 0;
先清空表数据，此时表依旧存在，Truncate不支持回滚，并且不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除；
注意如果不加KEEP 0的话，表空间是不会释放的。
2.迁移表数据
在出现以下错误时，我们不仅可以通过整理表空间或者清楚回收站进行解决，还可以通过将待释放表空间的表数据全部迁移到其他表空间，将空间释放后再迁移回原表空间。
--需移动的表数据select DISTINCT 'alter table '|| owner||'.'||segment_name || ' move tablespace user_test;' from dba_extents where segment_type='TABLE' and file_id=4;--需移动的索引数据select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild tablespace user_test;' from dba_extents where segment_type='INDEX' and file_id=4;--需移动的分区表数据select DISTINCT 'alter table '|| owner||'.'|| segment_name || ' move partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='TABLE PARTITION' and file_id=4;--需移动的分区表索引数据select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='INDEX PARTITION' and file_id=4;
上述代码中file_id可以通过查看表空间进行比对更改，上述代码执行后可以获得对应的自动生成的SQL代码，此时运行对应的SQL代码即可迁移表数据
参考文档
Oracle 在删除表、表空间、用户时 如何释放磁盘空间
【Oracle】ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据
Oracle查看表空间数据文件SQL
Oracle 删除数据后释放数据文件所占磁盘空间(表空间降高水位)
oracle 如何查看当前用户的表空间名称
Resize Datafile时ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据
先自我介绍一下，小编上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦
