表空间回收测试

表空间膨胀回收测试

1测试说明

本次测试主要在进行插入数据、删除数据后查看表空间膨胀情况以及后续回收相关测试

2概述

• 1.高水位线
• 数据库中数据文件的使用空间和未使用空间的分界；在insert数据时会进行上涨；即使是进行delete数据后，也无法降低高水位线；
• 2.数据膨胀
• 数据库为了性能，只标记某块空间是否可以复用而不主动回收已经分配的磁盘空间；
• ①delete数据后，行被标记删除，空间标记为可复用状态，但是高水位不降；
• ②update数据后，当行变长了，原来的位置放不下更新后的数据。此时更新后的数据会被放到新的位置，原来的空间会留下内部碎片导致空间膨胀；
• ③乱序insert数据后，会发生页分裂导致每一页的利用率很低；
• ④进行索引维护后索引页会分裂、碎片化，且二级索引比主键索引膨胀更严重；
• 3.解决方法
• ①可通过语法alter tablespace “表空间名称” resize datafile ‘数据文件’ to XXX;对表空间进行回收操作；
  ②将数据挪到新表空间，删除原表空间；

2测试环境

X86_64 4c

操作系统: Kylin V10 SP3

内存:6648

数据库版本: 03134284552-20260414-322369-20221

初始化参数: extent_size=32 page_size=32 charset=0 case_sensitive=1 blank_pad_mode=1

#其他参数依据参数优化脚本V5.6进行调整

3语句记录

-- 创建测试表空间
create tablespace TB_DATA datafile '/data/dmdata/TB_DATA01.DBF' size 128 autoextend on next 128;
--查询表空间利用率
SELECT
    a.tablespace_name "表空间名称",
    total / (1024 * 1024) "表空间大小(M)",
    free / (1024 * 1024) "表空间剩余大小(M)",
    (total - free) / (1024 * 1024) "表空间使用大小(M)",
    ROUND((total - free) / total, 4) * 100 "使用率 %"
FROM
    (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space WHERE tablespace_name = 'TB_DATA' GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files WHERE tablespace_name = 'TB_DATA' GROUP BY tablespace_name) b
WHERE
    a.tablespace_name = b.tablespace_name;
--创建测试用户绑定测试表空间并加权限
create user TEST IDENTIFIED BY "Test@123456" DEFAULT TABLESPACE TB_DATA DEFAULT  INDEX TABLESPACE TB_DATA;
grant "RESOURCE","SOI","SVI","VTI"  to TEST;
--创建表
CREATE TABLE "TEST"."TEST1"("ID" BIGINT ,"NAME" VARCHAR(100)) TABLESPACE TB_DATA;
--插入数据
DECLARE
  V_START_TIME TIMESTAMP;
  V_BATCH_SIZE INT := 10000;      -- 每批插入 1 万行
  V_TOTAL      INT := 0;
  V_TARGET     INT := 2000000;   -- 目标总量：200万行
BEGIN
  V_START_TIME := SYSDATE;

  WHILE V_TOTAL < V_TARGET LOOP
  INSERT INTO TEST.TEST1("ID","NAME")
  SELECT DBMS_RANDOM.VALUE(1,99999999999),
  TRUNC(DBMS_RANDOM.VALUE(1,1000))
  FROM DUAL
  CONNECT BY ROWNUM<=V_BATCH_SIZE;

    V_TOTAL := V_TOTAL + V_BATCH_SIZE;
    COMMIT;                             -- 每 1 万行提交一次

    IF MOD(V_TOTAL, 100000) = 0 THEN
      PRINT '已插入: ' || V_TOTAL || ' 行，已运行: ' || DATEDIFF(SECOND, V_START_TIME, SYSDATE) || ' 秒';
    END IF;
  END LOOP;

  PRINT '========== 完成 ==========';
  PRINT '总插入行数: ' || V_TOTAL;
  PRINT '总耗时: ' || DATEDIFF(SECOND, V_START_TIME, SYSDATE) || ' 秒';
END;
/
SELECT COUNT(*) FROM TEST.TEST1;
--创建普通索引
CREATE OR REPLACE  INDEX IDX_TEST1_ID ON "TEST".TEST1("ID" ASC) TABLESPACE TB_DATA;
CREATE OR REPLACE INDEX IDX_TEST1_NAME ON "TEST"."TEST1"("NAME") TABLESPACE TB_DATA;
--删除索引
DROP INDEX "TEST"."IDX_TEST1_ID";
DROP INDEX "TEST"."IDX_TEST1_NAME";
--删除数据
DECLARE
i int;
begin
for i in 1..2000 loop
delete from TEST.TEST1 where rownum<=1000;
commit;
end loop;
end;
/
--查找表ID
SELECT ID,NAME
FROM V$TABLESPACE;
--通过表ID进行手动回收
SP_PURGE_TS(5);
--查询可以回收的表空间大小
--smallest-WHM显示多少就可以resize到多少。
SELECT
    c.name tablespace_name ,a.path file_name,  b.ts_id,b.file_id,
    page  / 1024.0 AS "Blk. size(Kb)",
    CEIL( NVL(b.hwm, 1) * (page/1024.0/1024.0) ) AS "smallest(Mb) - HWM",   --向上取整
    a.TOTAL_SIZE * (page/1024.0/1024.0) AS "currsize(Mb)",
    a.TOTAL_SIZE * (page/1024.0/1024.0) -  NVL(b.hwm, 1) * (page/1024.0/1024.0)  AS "savings(Mb)"

FROM
    V$datafile a
LEFT JOIN
    (
SELECT
         ts_id, file_id,
         MAX(1.0*V_EXT.EXTENT_ID * SF_GET_EXTENT_SIZE + V_EXT.USED ) AS hwm
     FROM
         V$EXTENTS as V_EXT  where state not in ('FREE')  and seg_id>0
     GROUP BY
         ts_id,file_id
    ) b ON (a.id = b.file_id and a.group_id=b.ts_id)  join v$tablespace c on a.GROUP_ID = c.ID

WHERE
    a.status$ != 0
    and a.path like '%TB_DATA01.DBF'  --改为指定的数据文件即可
ORDER BY
"smallest(Mb) - HWM" DESC, tablespace_name, file_name;

--回收表空间大小
ALTER TABLESPACE "TB_DATA" RESIZE DATAFILE '/data/dmdata/TB_DATA01.DBF' TO 128;
ALTER TABLESPACE "TEST_DATA" RESIZE DATAFILE '/data/dmdata/TEST_DATA01.DBF' TO 188;
--回收失败时处理情况
-- 1. 重建索引方式
ALTER INDEX TEST.IDX_TEST1_NAME REBUILD;
ALTER INDEX TEST.IDX_TEST1_ID REBUILD;
--2.SP_REORGANIZE_INDEX重组索引方式
SP_REORGANIZE_INDEX('TEST','IDX_TEST1_NAME');
SP_REORGANIZE_INDEX('TEST','IDX_TEST1_ID');
--3移动到新表空间方式
--查看表所在的表空间
SELECT PATH,CLIENT_PATH FROM V$DATAFILE;
--创建目的表空间
create tablespace TEST_DATA datafile '/data/dmdata/TEST_DATA01.DBF' size 128 autoextend on next 128;
--挪动表到目的表空间中
declare
tablename varchar;
BEGIN
for rec in (select  owner,OBJECT_NAME from DBA_OBJECTS where owner in (select owner from DBA_TABLES t
where t.TABLESPACE_NAME='TB_DATA' ) and DBA_OBJECTS.OBJECT_TYPE = 'TABLE' ) loop
tablename := rec.OBJECT_NAME;
--print rec.OBJECT_NAME;
execute IMMEDIATE 'alter table ' ||rec.owner ||'.'||tablename||' move tablespace "TEST_DATA";';
end loop;
end;
--修改用户对应的表空间
ALTER USER TEST DEFAULT TABLESPACE TEST_DATA;
ALTER USER TEST DEFAULT index TABLESPACE TEST_DATA;
--查看目的表空间情况
SELECT
    a.tablespace_name "表空间名称",
    total / (1024 * 1024) "表空间大小(M)",
    free / (1024 * 1024) "表空间剩余大小(M)",
    (total - free) / (1024 * 1024) "表空间使用大小(M)",
    ROUND((total - free) / total, 4) * 100 "使用率 %"
FROM
    (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space WHERE tablespace_name = 'TEST_DATA' GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files WHERE tablespace_name = 'TEST_DATA' GROUP BY tablespace_name) b
WHERE
    a.tablespace_name = b.tablespace_name;

--查看表空间上的表
SELECT OWNER,TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = 'TEST_DATA';
SELECT OWNER,TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = 'TB_DATA';
--查看目的表空间可回收表大小
SELECT
    c.name tablespace_name ,a.path file_name,  b.ts_id,b.file_id,
    page  / 1024.0 AS "Blk. size(Kb)",
    CEIL( NVL(b.hwm, 1) * (page/1024.0/1024.0) ) AS "smallest(Mb) - HWM",   --向上取整
    a.TOTAL_SIZE * (page/1024.0/1024.0) AS "currsize(Mb)",
    a.TOTAL_SIZE * (page/1024.0/1024.0) -  NVL(b.hwm, 1) * (page/1024.0/1024.0)  AS "savings(Mb)"

FROM
    V$datafile a
LEFT JOIN
    (
SELECT
         ts_id, file_id,
         MAX(1.0*V_EXT.EXTENT_ID * SF_GET_EXTENT_SIZE + V_EXT.USED ) AS hwm
     FROM
         V$EXTENTS as V_EXT  where state not in ('FREE')  and seg_id>0
     GROUP BY
         ts_id,file_id
    ) b ON (a.id = b.file_id and a.group_id=b.ts_id)  join v$tablespace c on a.GROUP_ID = c.ID

WHERE
    a.status$ != 0
    and a.path like '%TEST_DATA01.DBF'  --改为指定的数据文件即可
ORDER BY
"smallest(Mb) - HWM" DESC, tablespace_name, file_name;
--确认表和索引等不在原空间上就可以删除原表空间了
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'TB_DATA';
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM DBA_INDEXES
WHERE TABLESPACE_NAME = 'TB_DATA';
-- 查看有碎片的空间占用情况
SELECT *
FROM V$EXTENTS E
JOIN V$SEGMENT_INFOS S ON E.SEG_ID = S.SEG_ID
WHERE S.TS_ID = (SELECT ID FROM V$TABLESPACE WHERE NAME = 'TB_DATA') AND STATE < > 'FULL';
--所有段空间占用
SELECT *
FROM V$EXTENTS E
JOIN V$SEGMENT_INFOS S ON E.SEG_ID = S.SEG_ID
WHERE S.TS_ID = (SELECT ID FROM V$TABLESPACE WHERE NAME = 'TB_DATA');

4测试流程

4.1 有连续空闲空间时ALTER TABLESPACE … RESIZE 方式

初始化表空间

创建表后

200万数据插入后

删除200万数据后并手动进行表大小回收

最终表空间大小为256=128(初始)+128(扩展)
表空间使用66MB
查询可回收表空间大小

进行alter tablespace … resize


此时表空间大小为128MB
测试结果说明膨胀后的表空间大小可以通过alter tablespace resize to 的方式进行回收表空间大小；

4.2 有连续空闲空间时将数据迁移到新表空间，删除原空间

初始化表空间

创建表后

200万数据插入后
删除200万数据后并手动进行表大小回收


创建目的表空间

将表移动到目的表空间中

修改用户到目的表空间

在目的表空间可以看到move来的表以及查看目的表空间的使用情况–表空间大小为128MB


此时查看原表空间上已经不存在TEST1这个表

测试结果说明可以通过将数据移动到新的表空间后就可以省区去回收表空间的步骤

4.3表空间首尾簇之间有半满簇(删除部分数据)时ALTER TABLESPACE … RESIZE 方式

初始化表空间

创建表后

200万数据插入后

插入数据后创建索引

删除部分数据(100万)后并手动进行表大小回收

最终表空间大小为256=128(初始)+128(扩展)

表空间使用203MB

查询可回收表空间大小发现最小可回收大小还是226

进行alter tablespace … resize to 203时，会显示无法回收簇的现象

差值23MB是刚才删除的数据的大小

查看当前空间分布情况，在表空间内部有大量半满簇(STATE=FRAG)

测试结果说明膨胀后的表空间内部有大量半满簇时不可以通过alter tablespace resize to 的方式进行回收表空间大小；

解决方案1使用alter index rebuild方式重建索引看是否可以

开始之前

重建索引

查看此时表空间使用情况，使用大小由203→250→182，说明释放了部分空间

(由于重建索引需要建立新的索引再删除原索引，所以会中途变大;)

查看此时可回收表空间情况，最小值反而变大了 由226→250，比重建索引前的情况更坏；

回收表空间失败

解决方案2使用SP_REORGANIZE_INDEX方式

当前表空间情况

当前可回收表空间情况

REORGANIZE_INDEX

进行查询表空间情况,使用大小由203→182,与重建索引的效果相同

查询当前可回收表空间情况发现smallest由226→208，比重建索引的效果好一些，但是依原有208-182=26MB的空间无法回收；

回收表空间失败

通过SP_REORGANIZE_INDEX方式不能把表内部空间回收

解决方案3使用将数据迁移到新表空间中，删除原空间的方式

当前表空间情况

当前可回收表空间情况

创建新的表空间

将表移动到目的表空间中

修改用户以及索引到目的表空间

查看目的表空间的使用情况

查看目的表空间可回收表大小情况

回收表空间

再查看表空间使用情况以及可回收表空间大小

虽然相比在原来的表空间(DB_DATA)中使用情况(182)较大(188)，但已经是三种方式中最好的情况

4.4无连续空闲空间(删除全部数据)时ALTER TABLESPACE … RESIZE 方式

初始化表空间

创建表后

200万数据插入后

插入数据后创建索引
删除全部数据(200万)后并手动进行表大小回收

最终表空间大小为256=128(初始)+128(扩展)
表空间使用68MB
查询可回收表空间大小发现最小可回收大小为185MB

进行alter tablespace … resize to 128(选择128是因为页大小32k时，最小的表空间大小为128MB)时，会显示无法回收簇的现象

差值是删除的数据的大小

查看当前空间分布情况

进行删除索引

查看表空间使用情况以及可回收表空间大小

将索引删除后，表空间使用大小返回到初始时刻;

此时再将表空间大小回收

查看表空间大小为128MB

删除全部数据后无需其他操作即可回收膨胀的表空间

4.5表空间创建两个表时，模拟表空间内部无连续空闲空间情况

在表空间中分别创建两个表test1、test2，分别插入200万数据后创建索引再删除第一张表的全部数据以及索引，查看此时空间占用情况以及簇占用等

初始化表空间

创建两张表

两张表都插入200万数据后

两张表都创建索引后

查看此时簇extent情况。从extent_id=65到extent_id=386为连续id，此时表示表空间中高水位线下的空间是被连续占用的情况

删除表test1表的所有数据以及索引后

查看此时簇extent情况。从extent_id=67到extent_id=135以及exent_id=206到extent_id=253之间的extent都不显示，这些消失的簇是刚才删除的数据以及索引所在位置；

表空间分布情况示意图

尝试使用迁移到新的表空间中的方式

创建新的表空间

把原表空间数据迁移到新的表空间中

查看新表空间使用情况以及可回收表空间大小

查看新表空间的extent使用情况。



此时查看原表空间的情况

原表空间中extent使用情况

进行回收表空间

可以选择把数据继续迁移到原的表空间中使用，也可以继续使用新的表空间

这里尝试把数据重新迁移到原表空间中

查看原表空间使用情况以及可回收表空间大小

查看此时原表空间extent使用情况

回收膨胀的表空间

查看表空间使用情况可以看到回收表空间成功

结果表明通过将数据移动到新的表空间中后会进行重新分配表空间中簇的使用情况从而解决表空间内部有空闲空间时高水位线不可下降带来的无法回收表空间的情况

5结论

5.1尾部为连续空间时

①有连续空闲空间时可以通过修改表空间大小的方式回收以膨胀的表空间大小。

②有连续空闲空间时可以通过将数据移动到新的表空间的方式，并确保原表空间无数据后可删除原表空间。

5.2表空间首尾簇之间有多数半满簇时

数据统计

	处理之前	重建索引	SP_REORGANIZE_INDEX	迁移数据到新表空间
表空间使用大小	203	182	182	188
最小可回收表空间大小	226	250	208	188

#单位 MB

#测试须等待UNDO_RETENTION时间后或通过手动方式SP_PURGE_TS(ID)形式确保数据准确

①删除部分数据后，表空间中有多数半满簇时，有三种方式进行回收表空间，其中效果最好的是通过将数据迁移到新表空间中，再将原表空间进行清除即可；

②若删除表中全部数据后，表空间使用依原很大，此刻只需要把索引删除后即可回收表空间

5.3尾部为不连续空闲空间时

只能通过移动数据到新的临时表空间中的方式才可以使空间中的簇重新分配从而降低高水位线进而回收表空间；重建索引以及重组索引的方式只能消除有多数碎片簇的情况而无法解决空间内部有大量空闲簇的情况。
https://eco.dameng.com