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

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

一、Shrink技术特性

可被移动对方回收空间：

不可移动对象​：

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

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

性能影响​：

收缩操作会移动数据，因此会消耗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_userDEFAULT TABLESPACE shrink_tsQUOTA 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 ONBEGINDBMS_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权限)
BEGINDBMS_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> BEGIN2    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_spaceGROUP 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 > 0AND segment_name IN (SELECT object_name FROM dba_objectsWHERE created < SYSDATE - 1/24
);
--
SEGMENT_NAME    SEGMENT_TYPE
_______________ _______________
SALES_DATA      TABLE
SALES_PK        INDEX

五、关键注意事项

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

•  最佳操作时间​

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

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