37-Oracle 23 ai Shrink Tablespace（一键收缩表空间）

关注

原创 2025-06-15 14:01:51 1.1k 阅读 AI 写同款· GEO 优化 ›

Oracle收缩表空间的简单方法在Oracle数据库中，收缩表空间是一种常见的维护操作，可以回收未使用的空间，减少表空间的碎片，提高性能。阅读详情

小伙伴们有没有经历过，超大表和超大数据的导入后，数据被删除了，然而空间迟迟不释放，存储添置又跟不上，业务空间告警的时候。收缩就很必须了，然而收缩需谨慎，数据大过天。DBMS_SPACE.SHRINK_TABLESPACE是Oracle 23ai新增功能，使得23 ai有了在线一键shrink tablespace的功能，19c及以下还需要手动操作。

一、Shrink技术特性

在Oracle 23ai中，大文件表空间收缩(Shrink Tablespace) 的核心机制是通过数据重组消除存储碎片，关键技术点如下：

空间碎片整理

当表被截断(TRUNCATE)或数据删除后，数据文件中会产生不连续的空白空间间隙(GAP)。收缩操作通过移动段对象将这些碎片整理到文件末尾。

在线段重组

使用DBMS_SPACE.SHRINK_TABLESPACE时：

自动识别可移动对象（表、索引等）
在线移动段数据（支持DML并发）
强制模式(TS_MODE_SHRINK_FORCE)处理不支持在线移动的对象

文件截断机制

碎片整理完成后，Oracle将空白空间从文件尾部截断，实现物理文件收缩。

操作模式

分析模式(TS_MODE_ANALYZE)：计算可回收空间

该模式不会实际移动数据或回收空间，而是分析表空间并返回一个报告，显示可以回收多少空间，以及建议的目标数据文件大小。它只是模拟收缩操作，不会对数据文件做任何修改。

收缩模式(TS_MODE_SHRINK)：执行实际空间回收

该模式会实际执行空间回收操作。它将移动表空间中可移动的段（如普通表、索引等），以压缩数据文件中的空间，然后截断数据文件以释放未使用的空间。

TS_TARGET_MAX_SHRINK：尽可能多地回收空间（默认）

指定一个具体的数值（以字节为单位）作为目标大小。

强制模式（TS_MODE_SHRINK_FORCE），

它会尝试移动那些在普通收缩模式下无法移动的段（通过离线移动等方式）。该模式会尝试对普通模式下无法移动的对象进行离线移动（Offline Move）。这可能会导致相关对象在移动期间不可用，因此使用时要谨慎。

可被移动对方回收空间：

普通堆表（Heap Table）
索引组织表（IOT）
索引（Index）
分区表的分区（Partition）
物化视图（Materialized View）
物化视图日志（Materialized View Log）

不可移动对象：

某些对象不能被移动，例如：

包含LOB列且LOB存储在单独段中的表（因为LOB段可能位于不同的表空间）
具有活动事务的对象
包含LONG类型的表
系统表空间（SYSTEM, SYSAUX）中的对象（尽管SYSAUX可以收缩，但有特殊限制）

空间回收限制：即使使用强制模式，仍然可能无法回收所有空间，

表空间中的某些对象无法移动（如上述不可移动对象）
数据文件中的某些空间是正在使用的，无法回收

性能影响：

收缩操作会移动数据，因此会消耗I/O资源。建议在业务低峰期进行。

依赖关系：

如果表空间中的对象有依赖关系（例如表上的索引），收缩操作会自动按正确顺序处理这些对象。

二、典型使用场景

批量数据清理后 - 截断/删除大表后回收空间
存储空间优化 - 解决文件系统空间不足问题
数据归档场景 - 历史数据迁移后回收空间
周期性维护 - 作为数据库健康检查的一部分

三、Oracle 23ai 实操脚本

-- 1. 创建测试环境
ALTER SYSTEM SET db_create_file_dest='/opt/oracle/oradata/FREE';
--System altered.

DROP USER IF EXISTS shrink_user CASCADE;
--User SHRINK_USER dropped.

DROP TABLESPACE IF EXISTS shrink_ts INCLUDING CONTENTS AND DATAFILES;
--TABLESPACE SHRINK_TS dropped.

CREATE BIGFILE TABLESPACE shrink_ts 
  DATAFILE SIZE 10M 
  AUTOEXTEND ON NEXT 1M;
--TABLESPACE SHRINK_TS created.

CREATE USER shrink_user IDENTIFIED BY shrink_user
  DEFAULT TABLESPACE shrink_ts
  QUOTA UNLIMITED ON shrink_ts;
--User SHRINK_USER created.

GRANT CREATE SESSION, CREATE TABLE TO shrink_user;
GRANT SELECT_CATALOG_ROLE TO shrink_user;
--Grant succeeded.

-- 2. 创建测试数据
sqlplus shrink_user/shrink_user@localhost:1521/FREEPDB1
--
[oracle@OL97 customer_orders]$ sqlplus shrink_user/shrink_user@localhost:1521/FREEPDB1
SQL*Plus: Release 23.0.0.0.0 - Production on Sun Jun 15 13:38:32 2025
Version 23.8.0.25.04
Copyright (c) 1982, 2025, Oracle.  All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SHRINK_USER@localhost:1521/FREEPDB1>
--
CREATE TABLE sales_data (
  id      NUMBER,
  details VARCHAR2(4000),
  CONSTRAINT sales_pk PRIMARY KEY (id)
);
--Table created.

INSERT /*+APPEND*/ INTO sales_data
SELECT 
  ROWNUM, 
  RPAD('X', 4000, 'X')
FROM dual
CONNECT BY LEVEL <= 100000;
COMMIT;
--
100000 rows created.
SHRINK_USER@localhost:1521/FREEPDB1>
Commit complete.
--收集信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'SALES_DATA');
SHRINK_USER@localhost:1521/FREEPDB1> EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'SALES_DATA');
PL/SQL procedure successfully completed.

-- 3. 检查初始空间分配
SELECT 
  tablespace_name, 
  ROUND(bytes/1024/1024, 2) AS size_mb 
FROM dba_data_files 
WHERE tablespace_name = 'SHRINK_TS';
--
TABLESPACE_NAME                   SIZE_MB
------------------------------ ----------
SHRINK_TS                             908
--
SELECT 
  table_name, 
  blocks,
  ROUND((blocks*8)/1024, 2) AS size_mb 
FROM user_tables;
--
TABLE_NAME       BLOCKS    SIZE_MB
_____________ _________ __________
SALES_DATA       100507     785.21

-- 4. 删表模拟数据清理
TRUNCATE TABLE sales_data;
EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'SALES_DATA');
-- 删除也可以模拟 模拟空间碎片
DELETE FROM SALES_DATA WHERE id <= 40000;
COMMIT;
--
SHRINK_USER@localhost:1521/FREEPDB1> DELETE FROM SALES_DATA WHERE id <= 40000;
40,000 rows deleted.
-- 5. 分析可回收空间 (SYS权限)
sqlplus / AS SYSDBA
SET SERVEROUTPUT ON

BEGIN
  DBMS_SPACE.SHRINK_TABLESPACE(
    tablespace_name => 'SHRINK_TS',
    shrink_mode    => DBMS_SPACE.TS_MODE_ANALYZE
  );
END;
/

---- 5. 预收缩空间分析
SELECT 
  segment_name,
  segment_type,
  blocks,
  bytes/1024/1024 size_mb
FROM user_segments;
--
SEGMENT_NAME    SEGMENT_TYPE       BLOCKS    SIZE_MB
_______________ _______________ _________ __________
SALES_DATA      TABLE              101248        791
SALES_PK        INDEX                 256          2

-- 6. 执行空间收缩(SYS权限)
BEGIN
  DBMS_SPACE.SHRINK_TABLESPACE('SHRINK_TS');
END;
/
--
[root@OL97 dbs]# su - oracle
[oracle@OL97 ~]$ sql / as sysdba;
SQLcl: Release 25.1 Production on Sun Jun 15 13:43:44 2025
Copyright (c) 1982, 2025, Oracle.  All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SYS@CDB$ROOT> alter session set container =FREEPDB1;
Session altered.
SYS@CDB$ROOT> BEGIN
  2    DBMS_SPACE.SHRINK_TABLESPACE('SHRINK_TS');
  3  END;
  4* /

PL/SQL procedure successfully completed.
SYS@CDB$ROOT>

-- 7. 验证收缩结果
SELECT 
  tablespace_name, 
  ROUND(bytes/1024/1024, 2) AS size_mb 
FROM dba_data_files 
WHERE tablespace_name = 'SHRINK_TS';
--
TABLESPACE_NAME       SIZE_MB
__________________ __________
SHRINK_TS                 862

-- 清理环境
DROP USER shrink_user CASCADE;
DROP TABLESPACE shrink_ts INCLUDING CONTENTS AND DATAFILES;

四、验证要点

空间回收验证

-- 收缩前后空间对比
SELECT 
  df.tablespace_name,
  SUM(df.bytes)/1024/1024 allocated_mb,
  SUM(df.bytes - NVL(fs.free_bytes, 0))/1024/1024 used_mb
FROM dba_data_files df
LEFT JOIN (
  SELECT 
    file_id, 
    SUM(bytes) AS free_bytes  -- 添加明确别名
  FROM dba_free_space
  GROUP BY file_id
) fs ON df.file_id = fs.file_id
WHERE df.tablespace_name = 'SHRINK_TS'  -- 添加表别名
GROUP BY df.tablespace_name;  -- 添加表别名
--
TABLESPACE_NAME       ALLOCATED_MB     USED_MB
__________________ _______________ ___________
SHRINK_TS                      862    861.0625

段移动监控

-- 实时监控收缩操作
SELECT 
  sql_id,
  sid,
  event,
  p1 TEXT,
  p2 BLOCKS_MOVED
FROM v$session
WHERE module = 'SHRINK_TBS';

查询依赖验证

-- 检查未收缩对象
SELECT segment_name, segment_type
FROM dba_segments
WHERE tablespace_name = 'SHRINK_TS'
  AND blocks > 0
MINUS
SELECT segment_name, segment_type
FROM dba_segments
WHERE tablespace_name = 'SHRINK_TS'
  AND blocks > 0
  AND segment_name IN (
    SELECT object_name 
    FROM dba_objects
    WHERE created < SYSDATE - 1/24
);
--
SEGMENT_NAME    SEGMENT_TYPE
_______________ _______________
SALES_DATA      TABLE
SALES_PK        INDEX

五、关键注意事项

在线操作限制标准模式仅处理支持在线移动的对象，强制模式可能导致短暂锁表
空间预留策略ALTER TABLESPACE ... AUTOEXTEND OFF 可在收缩前禁用自动扩展
系统表空间支持SYSAUX表空间可收缩，但SYSTEM表空间不支持
操作监控通过 V$SESSION_LONGOPS 监控收缩进度：

SELECT sid, serial#, opname, sofar, totalwork 
FROM v$session_longops 
WHERE opname LIKE 'SHRINK%';

最佳操作时间

- 在业务低峰期执行
- 收缩前进行完整备份
- 优先使用分析模式评估收益

Oracle 23ai的大文件表空间收缩技术彻底解决了传统表空间空间回收需依赖操作系统的问题，结合OMF(Oracle Managed Files)特性，实现了端到端的自动化存储管理。通过在线重组实现存储空间的高效回收，显著降低存储成本。

标签

#oracle #数据库 #大数据 #database #sql

Oracle收缩表，释放占用的存储空间（shrink space） 收缩表（shrink space cascade），整理碎片，降低高水位。阅读详情

37-Oracle 23 ai Shrink Tablespace（一键收缩表空间）

一、Shrink技术特性

​空间碎片整理​

在线段重组​

​文件截断机制​

操作模式​

可被移动对方回收空间：

不可移动对象​：

空间回收限制​：即使使用强制模式，仍然可能无法回收所有空间，

性能影响​：

依赖关系​：

二、典型使用场景

三、Oracle 23ai 实操脚本

四、验证要点

五、关键注意事项

相关推荐

空间碎片整理

在线段重组

文件截断机制

操作模式

不可移动对象：

空间回收限制：即使使用强制模式，仍然可能无法回收所有空间，

性能影响：

依赖关系：