Oracle 空间回收踩坑实录删了几百G数据表空间却一点没降下来做这行十年DBA 或偏数据库运维的朋友应该都遇到过同一种诡异情形一张大表按条件 DELETE 掉了几百 GB 的历史数据业务侧反馈“空间已经清理完了”你打开 dba_data_files 一看数据文件大小纹丝不动剩余空间一点没多出来。更头疼的是你尝试用ALTER DATABASE DATAFILE ... RESIZE收缩文件直接给你抛一个ORA-03297: file contains data in use beyond specified RBS high water mark这就是 Oracle 空间回收里最经典的那个“回收不了”的坑。这篇文章不聊理论书上的概念就聊我最近处理的一个真实案例。背景很简单一套生产库里的订单流水表数据量从接近 800GB 删到了 120GB 左右但表空间文件始终停在 810GB 下不去磁盘告警一直在业务又不敢重启。整条排查和处理的链路跑下来我总结出的核心经验是在 Oracle 里“数据删了”和“空间能回收”之间隔着一道叫高水位线(HWM)的墙还有一堆看起来能回收但根本动不了的特殊表空间类型。下面我把整个排查过程、翻车细节、以及最后怎么把空间真正还给操作系统的完整操作步骤都写出来希望能帮到正在被同类问题折磨的同行。这套经验同样适用于 Oracle 11g、12c、19c逻辑基本一致。1. 先给问题定性空间“没回收”可能发生在两个层面很多人一上来就执行RESIZE报错后又一脸懵。我的习惯是先花十分钟搞清楚一件事空间到底卡在哪一层。在 Oracle 里空间回收通常涉及两个完全不同的层面它们的处理手段甚至原理互相冲突。1.1 第一步确认表空间文件大小 vs 段内空间第一个层面是“操作系统文件层”。你执行ALTER DATABASE DATAFILE /u01/app/oracle/oradata/PROD/users01.dbf RESIZE 200G这是想让 Oracle 把物理文件缩小把空间还给操作系统。第二个层面是“段内空间层”。表空间里的表、索引这些段对象内部存在大量已经被标记为“空闲”但还没有被重新使用的数据块。这些空间 Oracle 自己知道是空的可以给后续 Insert 用但你没法直接把它从文件里抠出来还给操作系统。我处理案例时的第一个动作就是查这两层分别是什么情况-- 查看数据文件大小和当前使用情况 SELECT file_id, file_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb, ROUND((bytes - 1024 * blocks) / 1024 / 1024 / 1024, 2) AS used_gb FROM dba_data_files WHERE tablespace_name USERS; -- 查看表空间里还有多少“空闲”扩展区 SELECT file_id, ROUND(SUM(blocks) * 8 / 1024 / 1024, 2) AS free_mb FROM dba_free_space WHERE tablespace_name USERS GROUP BY file_id;注意这里dba_free_space统计的其实是段对象里已经释放、可以被重新分配的空间也就是第二个层面的“段内空闲”。当时我看到的结果是文件 810GBdba_free_space里统计出来的空闲也有 400 多 GB。按常识理解既然空闲这么多文件为什么不能缩小这就牵出了ORA-03297的真相数据文件末尾附近还残留着数据块extentOracle 不允许你直接把文件尾巴切掉。一个数据文件可以理解成一条长长的街你要把围墙往后移但街尾还有几户人家没搬走你当然动不了。实际生产里表经过长时间的 DELETE 和 INSERT那些剩余的数据块会像撒芝麻一样散落在文件的各个位置文件尾部尤其容易出现“明明是空文件尾但尾端前面一点还有段在占用”的情况。1.2 ORA-03297 这条报错到底在说什么这条报错很多人查文档都查得云里雾里其实拆开看就一句话你要把文件收缩到某个大小RBS即指定收缩后的 high water mark 位置但文件里实际数据的使用位置超出了这个目标值。更准确说在这个目标位置之后仍然存在未被释放的 extent。我当时的处理是先用一个最长用的查询看看这个文件里到底是哪些段对象在“阻挠”收缩SELECT owner, segment_name, segment_type, extent_id, ROUND(blocks * 8 / 1024, 2) AS extent_mb FROM dba_extents WHERE tablespace_name USERS ORDER BY file_id, block_id DESC;结果很典型占着文件尾部的是几张明细表和几个索引它们虽然已经删了大量数据但段自身没有收缩过。这时候你基本可以断定真正的问题出在第二个层面也就是段对象的高水位线HWM没有降下来。顺着这个思路解决方向就变成了如何把段的 HWM 降下来让文件的尾部彻底腾空。2. 高水位线DELETE 掉几十 G 空间却分毫未动的真凶高水位线High Water MarkHWM这个概念没做过深入调优的 DBA 可能一直只停留在面试题层面。但凡是做过空间回收的人都会被它狠狠上一课。2.1 高水位线与低水位线的本质差异Oracle 的表段里数据块被分成三种状态已经格式化并且曾经包含过数据的块HWM 以下、从未使用过的块HWM 以上、以及 HWM 以下但当前为空、可以复用的块。HWM 就是“这个段曾经插入数据到达过的最远位置”的标记。我用一个接水杯的例子来解释一张表就是一个水杯。你往里倒水插入数据水面会升到某个位置这个位置就是 HWM。现在你把水倒掉一半DELETE 数据水面下降到某个位置但杯壁上留下的水渍痕迹HWM还停留在原来最高的位置。Oracle 再往这个杯子里加水新 INSERT是先加到水渍痕迹以下那些已经空出来的区域还是直接冲破水渍痕迹往上加答案是HWM 以下的空间会被优先复用HWM 不会自动降低。那“低水位线”又是怎么回事在 ASSM自动段空间管理下Oracle 对 HWM 以下的空间也不是一刀切管理它内部还有一个“低高水位线”low HWM的概念。简单理解段里有一些块是“完全没数据、但已经被格式化过”的块这些块在低 HWM 和高 HWM 之间Oracle 知道它们是空的会优先分配它们。但整段空间的使用水位依然以高 HWM 为准。DELETE操作只负责把行数据清掉并标记块为可用它永远不会去移动 HWM。所以在 Oracle 的视角里段占用的空间依然那么大数据文件自然一点都让不出来。2.2 判断 HWM 上方有多少“可回收空间”处理案例时我用了 Oracle 官方提供的过程DBMS_SPACE.SPACE_USAGE它可以精确查出一个段在当前表空间管理模式下已经用到高水位线的程度。这个包在 9i 以后就有很多 DBA 反而不知道用。SET SERVEROUTPUT ON DECLARE v_fs1 NUMBER; v_fs2 NUMBER; v_fs3 NUMBER; v_fs4 NUMBER; v_fs5 NUMBER; v_fs6 NUMBER; v_fs7 NUMBER; v_fs8 NUMBER; v_full_blocks NUMBER; v_unformatted_blocks NUMBER; BEGIN DBMS_SPACE.SPACE_USAGE( APP, ORDER_HIS, TABLE, NULL, v_fs1, v_fs2, v_fs3, v_fs4, v_fs5, v_fs6, v_fs7, v_fs8, v_full_blocks, v_unformatted_blocks ); DBMS_OUTPUT.PUT_LINE(FS1(0-25% free): || v_fs1); DBMS_OUTPUT.PUT_LINE(FS2(25-50% free): || v_fs2); DBMS_OUTPUT.PUT_LINE(FS3(50-75% free): || v_fs3); DBMS_OUTPUT.PUT_LINE(FS4(75-100% free): || v_fs4); DBMS_OUTPUT.PUT_LINE(FULL blocks: || v_full_blocks); DBMS_OUTPUT.PUT_LINE(UNFORMATTED blocks: || v_unformatted_blocks); END; /执行完这个脚本我当时的数字现在还记得这张表有大量块处于 FS4 状态75% 以上空间空闲说明 DELETE 之后块内留下来的空位置多到离谱但这些空间还是属于这个段的。它们可以被后续 INSERT 复用却不能直接还给操作系统。还有一个更简单的判断方式统计一下表的行数和实际占用如果逻辑读远大于应有的块数基本就是 HWM 太高导致的空块太多。不过生产系统别随便做全表扫描我只在低峰期跑了一次。2.3 为什么 DELETE 完后看不到效果到这里你应该明白一个扎心的事实DELETE 本身根本不是空间回收的手段它只是“制造可用空间”的手段。真正能实现空间回收的操作只有三种TRUNCATE、SHRINK SPACE、ALTER TABLE MOVE。TRUNCATE 会直接重建段并把 HWM 拉回零但它也会清掉全部数据、无法带条件生产环境大部分场景用不了。所以 DELETE 完之后你看数据文件大小它当然纹丝不动因为 HWM 还停在那儿段还认为自己占着那么多地盘。我经常跟同事说的一句话是“DELETE 只是把桌子上的文件收进了抽屉但桌子还是占着那么多地方。” 你要让桌子变小得把抽屉里的东西也清掉或者换个桌子——对应到 Oracle就是下面要说的 SHRINK 和 MOVE。3. 在线方案SHRINK SPACE 的正确姿势与翻车风险SHRINK 是 Oracle 10g 开始提供的段收缩功能它号称能在不锁表、不影响在线业务的前提下降低 HWM。听起来很美用起来坑也不少。3.1 SHRINK 的原理和两个硬性前提原理上说SHRINK 分两步走先在段内部把数据行尽量往 HWM 以下的块里挪动把空块腾出来然后 Oracle 直接移动 HWM 到新的位置那些 HWM 以上的空块就从“段占用的空间”变成了“表空间的空闲空间”。这一步做完dba_free_space里立马会多出一大批空间数据文件再收缩就有希望了。但 SHRINK 有两个硬性前提缺一个都跑不起来表空间必须使用 ASSM 管理。也就是创建表空间时要写SEGMENT SPACE MANAGEMENT AUTO。如果是手动段空间管理MSSMSHRINK 直接提示ORA-10635: Invalid segment or tablespace type因为 SHRINK 依赖 ASSM 的位图状态来定位空块。表本身要开启 ROW MOVEMENT。SHRINK 在块间移动数据行行的物理地址rowid会变化Oracle 为了安全默认不让你动。必须先执行ALTER TABLE ... ENABLE ROW MOVEMENT。第二个前提很多人会犹豫担心开启 ROW MOVEMENT 会影响业务。实际上这个属性只影响基于 rowid 的查询和物化视图绝大多数 OLTP 业务不会直接依赖 rowid开启后并不影响正常 SQL。你只需要让业务方知道这件事并且知会一下有触发器、依赖 rowid 的极端场景需要额外评估。3.2 具体操作命令与执行顺序我当时的操作顺序是这样写的-- 1. 开启行移动 ALTER TABLE APP.ORDER_HIS ENABLE ROW MOVEMENT; -- 2. 收缩段CASCADE 会连同表上的索引一起收缩 ALTER TABLE APP.ORDER_HIS SHRINK SPACE CASCADE; -- 3. 如果空间还是不够可以把段一次压到最小 ALTER TABLE APP.ORDER_HIS SHRINK SPACE COMPACT;多提一句CASCADE。它会顺带把该表上的普通索引也做收缩避免你收完表空间却发现索引的 HWM 没降。但 CASCADE 也意味着所有索引上的行会移动收缩过程中会产生大量 UNDO 和 REDO时间也会明显变长。生产环境我的建议是先只做表收缩不要带 CASCADE等表收缩完后重建大索引这样可以控制每一步的风险。执行过程中可以从另一个会话观察 V$SESSION_LONGOPS看收缩任务到哪个阶段了。这步很关键因为大表的 SHRINK 不是秒级完成的跑十几分钟很正常你不盯着心里不踏实。3.3 翻车风险函数索引 ORA-10631 和 UNDO 膨胀SHRINK 最经典的翻车点是表上存在函数索引基于函数的索引如SUBSTR(column, 1, 10)这种。只要表上有函数索引执行 SHRINK 大概率会得到ORA-10631: SHRINK clause should not be specified for this object为什么因为函数索引的索引条目是根据函数计算结果生成的SHRINK 移动行时无法高效维护这种非普通索引结构。Oracle 干脆一刀切函数索引的表不允许 SHRINK。我那次碰到的表恰好就有个TO_CHAR(create_date, YYYYMMDD)的函数索引第一次执行就撞上了这个错。两个选择要么先 drop 函数索引再 SHRINK回头再重建要么直接跳过 SHRINK 走下面的 MOVE 方案。因为函数索引往往是为了特定报表存在的drop 和重建需要业务审批我那次选了后者。还有一个容易忽略的问题SHRINK 过程中数据行大量移动UNDO 表空间会猛涨。它不是在线 DDL 那种纯字典操作而是实打实的数据搬移每一行被挪动都会记录 UNDO。如果你的 UNDO 表空间本来就紧张SHRINK 可能把 UNDO 撑爆报出ORA-30036: unable to extend segment by ... in undo tablespace。这属于“为了回收空间反而制造空间危机”的典型事故场景。3.4 收缩完之后如何验证结果收缩完成后别急着去 RESIZE 数据文件先确认两件事段变小了没有、文件尾部还有没有对象占位。-- 对比收缩前后的段大小 SELECT segment_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb, blocks, extents FROM dba_segments WHERE segment_name ORDER_HIS AND owner APP;如果段的大小已明显下降再回文件层面看dba_free_space你会发现空闲空间也成倍增加了。此时从文件头部开始连续的空闲 extent 已经能覆盖你要收缩到的目标位置RESIZE 的条件才真正具备。提醒一下SHRINK 后建议顺手做一个ALTER TABLE ... MOVE之外的表分析也就是DBMS_STATS.GATHER_TABLE_STATS因为 SHRINK 会改变段内的物理分布统计信息里关于块数的数据已经不准了不重新收集会让 CBO 走错执行计划。4. 离线方案ALTER TABLE MOVE 的时序细节与重建索引如果 SHRINK 因为函数索引走不通或者表特别大、SHRINK 时间太长那就只能用传统手艺ALTER TABLE MOVE了。这个方案本质上是把表整个重写一遍到新的段里新段的 HWM 自然是从零开始的空间回收效果立竿见影但它离线要考虑的影响面比 SHRINK 大得多。4.1 MOVE 和 SHRINK 怎么选我根据自己的实操体会把两者放到一张表里对比选型时直接照着看对比项SHRINK SPACEALTER TABLE MOVE在线性在线不阻塞 DML离线执行期间表不可写是否能带条件不能只能整表不能只能整表函数索引不支持ORA-10631支持但重建索引时要想办法索引维护CASCADE 时自动索引全部失效必须重建空间需求需要少量 UNDO需要额外一个完整段大小的空间执行速度相对慢逐块搬相对快但会锁表对统计信息影响需要重收需要重收结论很简单在线要求高、空间余量充足、没有函数索引的表用 SHRINK能接受停机、或 SHRINK 遇到硬性阻碍的表用 MOVE。千万别在业务高峰期做 MOVE哪怕你觉得自己操作很快一个几 GB 的表复制起来也要好几分钟这期间所有涉及这张表的应用都会卡死或报错。4.2 完整操作步骤与索引重建的节奏我当时对另一张大表PAY_LOG执行了 MOVE具体操作是这么做的-- 1. 记录当前表上的所有索引后面要重建 SELECT index_name, index_type, tablespace_name FROM dba_indexes WHERE table_owner APP AND table_name PAY_LOG ORDER BY index_name; -- 2. 执行表搬迁这里直接指定搬到另一个空闲表空间 ALTER TABLE APP.PAY_LOG MOVE TABLESPACE DATA_BIG; -- 3. 检查索引状态此时应该全部 UNUSABLE SELECT index_name, status FROM dba_indexes WHERE table_owner APP AND table_name PAY_LOG; -- 4. 重建所有索引 ALTER INDEX APP.PK_PAY_LOG REBUILD; ALTER INDEX APP.IDX_PAY_LOG_01 REBUILD;注意第 2 步我特意指定了TABLESPACE DATA_BIG也就是说把表搬到了另一个空间更大的表空间。这是 MOVE 方案很关键的一个技巧如果你在原表空间内 MOVEOracle 需要同时存在新旧两个段空间稍微不够就会ORA-01658: unable to create INITIAL extent for segment in tablespace。换到一个空间富裕的表空间相当于把收缩问题转移成了“搬个家”旧表空间里的原段自然空出来一大片直接 RESIZE 旧文件就行了。步骤 4 里重建索引也是有好几个坑的。如果你索引很多、很大全部重建会花很久最稳妥的做法是重新创建而不是REBUILD——REBUILD 会在原表空间里新建索引段如果原表空间已经快满了也可能失败而CREATE ... INDEX ... TABLESPACE DATA_BIG可以把新索引放到目标表空间顺便完成索引的物理碎片整理。4.3 MOVE 过程中最容易忽视的三个小毛病第一个毛病是MOVE 会改变表的物理布局但没有自动更新统计信息。上面提到的表分析MOVE 之后务必执行一遍否则 CBO 可能基于旧的块数信息做出错误的全表扫描判断。第二个毛病是MOVE 之后的索引名别搞混。如果表上有主键约束索引名往往是系统自动生成的那种SYS_C00xxxxx重建时要先查询出真实索引名而不是猜。我当时就差点把一个SYS_IL...大对象索引当成普通索引重建那玩意儿根本不能手动 REBUILD。第三个毛病是不要忘记处理 LOB 字段。如果表里带 BLOB/CLOB 字段ALTER TABLE MOVE默认只移动表段LOB 段是原地不动的。你要用MOVE ... LOB (col_name) STORE AS (TABLESPACE DATA_BIG)这种写法把 LOB 段也一起搬走否则空间照样收不干净。很多 DBA 在这上面吃过亏表大小从 800GB 变成 10GB但表空间文件还是 600GB一查全是 LOB 段占着位置。5. 临时表空间与 UNDO 的回收另一个层面的空间坑主表的问题解决后我顺手检查了系统里的其他表空间发现还有两个“空间回收困难户”——临时表空间(TEMP)和 UNDO 表空间。这俩和普通数据表空间完全两码事处理不好同样会把你折腾到崩溃。5.1 TEMP 表空间为什么也卡住临时表空间是 Oracle 用来做排序、哈希连接、临时表数据存的区域。很多系统里 TEMP 文件设了几十上百 GB运行一段时间发现它占满磁盘了你想ALTER DATABASE TEMPFILE ... RESIZE收缩同样会遇到报错——因为临时文件里还有活动的临时段或者即便没有活动会话临时段空间也没有被完整释放回文件尾部。我的处理流程是这样-- 1. 查看当前有哪些会话正在使用临时段 SELECT se.sid, se.username, se.tablespace, se.segtype, se.blocks * 8 / 1024 AS mb FROM v$sort_usage se ORDER BY se.blocks DESC; -- 2. 没有活动会话后直接收缩临时表空间 ALTER TABLESPACE TEMP SHRINK TEMPFILE /u01/app/oracle/oradata/PROD/temp01.dbf;ALTER TABLESPACE ... SHRINK TEMPFILE是 11g 以后才有的功能专门用来收缩临时表空间文件。如果等不及、或者收缩效果不理想也可以重建临时表空间新建一个小的 TEMP 表空间把数据库默认临时表空间切过去drop 掉旧的。这套操作比普通表空间简单因为临时表空间里的东西本身不需要持久化随便搬但要注意别在业务时间做——切换默认临时表空间会让正在运行的排序操作使用到新建的临时表空间如果你新文件设太小反而会引发ORA-01652: unable to extend temp segment。5.2 UNDO 表空间越界收缩的骚操作UNDO 表空间更阴险。它不是按数据文件里的 extent 空不空来判断能否收缩的而是受UNDO_RETENTION控制。Oracle 希望保留足够多的 UNDO 来保证一致性读所以即便没有活动事务它也可能觉得自己还“需要”那么大的 UNDO 文件。更坑的是从 10g 开始 Oracle 会自动调整 UNDO 保留时间TUNED_UNDORETENTION你手动设的UNDO_RETENTION900没什么用它实际会按查询时长自动调大于是 UNDO 表空间只涨不缩。我当时的情况UNDO 文件 120GBdba_free_space 显示空闲 90GB但 RESIZE 就是不让过。查V$UNDOSTAT后看到TUNED_UNDORETENTION被自动拉到了几千万毫秒等于 Oracle 认为要保留好几个小时的历史数据只能按下面的步骤强制收缩-- 1. 建一个小的新 UNDO 表空间 CREATE UNDO TABLESPACE UNDO_NEW DATAFILE /u01/app/oracle/oradata/PROD/undo_new01.dbf SIZE 10G AUTOEXTEND ON; -- 2. 切换默认到新表空间 ALTER SYSTEM SET UNDO_TABLESPACE UNDO_NEW; -- 3. 等现有事务跑完后drop 掉旧 UNDO 表空间 DROP TABLESPACE UNDO_OLD INCLUDING CONTENTS AND DATAFILES;这套操作里的关键点是先用新表空间顶替旧表空间旧 UNDO 表空间才能被整体 drop。你没法直接在原文件上把它缩到很小因为 Oracle 不允许你在当前不用的 UNDO 表空间上做太激进的缩小。切换之后如果还担心文件自动增长记得关掉新表空间的 AUTOEXTEND或者设一个 MAXSIZE 上限避免过几个月又涨到满。6. 复盘整个案例从接到告警到空间落袋的完整链路最后做一次复盘把整个案例从开始到收尾的完整链路串起来也给后面的空间维护提几个实用建议。一条链路走完你大概会理解为什么所有人都说“Oracle 空间回收不是删数据那么简单”。6.1 本次处理的完整时间线我这次实战的处理顺序严格来说是下面这样接到磁盘告警先查dba_data_filesdba_free_space确认空间卡在 USERS 表空间。试图 RESIZE 直接撞上ORA-03297再查dba_extents定位是哪些段占着文件尾部。对占尾部的目标表ORDER_HIS用DBMS_SPACE.SPACE_USAGE判断 HWM 以上空闲块比例确认是典型的 DELETE 后 HWM 不降问题。方案一执行 SHRINK因为函数索引撞上ORA-10631放弃。方案二对ORDER_HIS做 MOVE提前规划好目标表空间 DATA_BIGMOVE 后重建全索引并收集统计信息。对另一张PAY_LOG直接走 MOVE顺带处理了 LOB 段。原 USERS 表空间文件尾部被腾空重新执行 RESIZE 到目标大小空间真正还给了操作系统。顺手检查 TEMP 和 UNDOTEMP 用SHRINK TEMPFILE解决UNDO 通过新建/切换/drop 三个动作完成收缩。整套下来大概花了不到四个小时如果一开始就闷头 RESIZE可能折腾一天还在原地打转。6.2 日常应该怎么盯空间避免再次走到回收这一步经历过这次之后我给自己的维护清单里加了几条强制项每周做一次段大小排序查dba_segments按字节排序找出增长最快的前十大对象而不是只看表空间总体使用率。很多空间问题都是“表空间没满但某个段已经大得离谱”。对大表设置定期归档策略核心流水表按月分区的话直接DROP PARTITION或TRUNCATE PARTITION是低成本的远好过 DELETE 之后做 SHRINK/MOVE。分区表才是大表数据清理的正道没有分区的表早晚要被 HWM 卡死。监控 RESIZE 可行性每次清理完大量数据后第一时间跑一个ALTER DATABASE DATAFILE ... RESIZE的预检查不要等磁盘满了才动手。6.3 不要“没事就收缩”的忠告最后一条建议可能会让某些人意外空间够用的话不要频繁对表做 SHRINK 或 MOVE。这两种操作本质上都会消耗大量 I/O、UNDO、REDO还会让热数据在缓存中的命中率下降。收缩一时爽之后积累的碎片和统计信息滞后可能让业务查询变慢。空间回收是清理动作的收尾是“不得不做”的修复操作而不该成为日常习惯。拿我们这个例子来说如果一开始表就设计成按月份分区历史分区到期直接 drop空间当天就释放了根本不需要 MOVE。所以真正治本的方案永远是设计层面的前瞻性。作为一个常年和数据文件打交道的 DBA我现在看到一张大表还在用 DELETE 清理历史都会多嘴问一句你的表分区了吗这句话比任何 SHRINK 脚本都值钱。