达梦表空间缩减步骤

项目中经常碰到表空间数据文件占用很大，但实际使用率很低的情况，达梦23年版本就支持回缩表空间命令，但在实际生产中执行回缩命令会出现无法回收簇的错误。现模拟相关场景

对测试表进行大批量insert，创建索引，然后删除该表中间的大量数据，表空间使用率查询如下

SELECT F.TABLESPACE_NAME,

ROUND(T.TOTAL_SPACE / 1024, 2) "TOTAL(GB)",

ROUND((T.TOTAL_SPACE - F.FREE_SPACE) / 1024, 2) "USED(GB)",

ROUND(F.FREE_SPACE / 1024, 2) "FREE(GB)",

ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100, 2) || '%' PER_FREE

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BLOCKS *

(SELECT PARA_VALUE / 1024

FROM V$DM_INI

WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024))

FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

结果如下：系统表空间main总大小12G，使用10G，空闲2G

数据库重启之后，表空间使用率如下，MAIN表空间总大小没变，空闲空间有4G

为释放磁盘空间，使用如下命令将表空间文件缩小到10G。提示无法回收簇

alter tablespace "MAIN" resize datafile 'MAIN.DBF' to 10000;

之后无论缩减多大，都有如上的报错。该功能实现方式可能只能裁剪掉MAIN.DBF表空间文件尾部空闲的部分。如果表空间尾部存在数据则无法回收，可以将表空间尾部数据进行迁移到其他地方，再进行缩减

查询表空间尾部对象的10个表

select distinct

case o.object_type when 'INDEX' THEN (select i.table_name

from DBA_INDEXES i

where i.INDEX_NAME=o.object_name limit 1)

when 'TABLE' THEN o.object_name else null end

as table_name,

o.owner

from v$extents e

join V$SEGMENT_INFOS s

on e.seg_id=s.seg_id

join dba_objects o

on o.object_id=s.obj_id

left join dba_indexes i

on o.object_name=i.index_name

where e.ts_id=(select t.id from v$tablespace t where name='MAIN')

order by e.extent_id desc limit 10;

查询到表空间文件尾部是哪些表之后，想办法将这些表转移到其他地方后（也可以使用逻辑导出方式），再执行缩减表空间语句。

1 新建一个单独的表空间，并设置表空间自动扩展： 

create tablespace TBS1 datafile 'TBS.DBF' size 512 autoextend on;

2. 将原表空间中的所有表通过下面的命令全部转移到上述新建的表空间：

alter table SYSDBA.EMP MOVE TABLESPACE TBS1;

alter table SYSDBA.DEPT MOVE TABLESPACE TBS1;

alter table SYSDBA.LOG_COMMIT MOVE TABLESPACE TBS1;

3. 使用表空间文件回缩命令进行表空间回收：还是一样报错

重启一下数据库，再次执行如下回收命令成功

4. 将表从新建的表空间中移动回原来的表空间

alter table SYSDBA.EMP MOVE TABLESPACE MAIN;

alter table SYSDBA.DEPT MOVE TABLESPACE MAIN;

alter table SYSDBA.LOG_COMMIT MOVE TABLESPACE MAIN;

迁移完之后，查询表空间使用率

附:批量将DMTEST模式下面的表从TBS1表空间迁移到TBS2表空间

begin

for r in(select owner,table_name from dba_tables where TABLESPACE_NAME='TBS1' AND OWNER = 'DMTEST') loop

begin

execute immediate 'alter table ' || r.owner || '."' || r.table_name || '" MOVE TABLESPACE TBS2';

end;

end loop;

end;