alter tablespace xx resize xx方式回收表空间

# 版本说明

在03134284044-20230417-187846-20040及之后的版本新增了回收表空间功能。

# 模拟表空间水平位升高

创建业务表空间

create tablespace TB_DATA datafile '/opt/dmdbms/bin/TESTDB/TB_DATA01.DBF' size 512 autoextend on next 1024;

查询表空间利用率

SELECT
        a.tablespace_name "表空间名称"                         ,
        total        / (1024 * 1024) "表空间大小(M)"                 ,
        free         / (1024 * 1024) "表空间剩余大小(M)"               ,
        (total       - free) / (1024 * 1024 ) "表空间使用大小(M)"      ,
        total        / (1024 * 1024 * 1024) "表空间大小(G)"          ,
        free         / (1024 * 1024 * 1024) "表空间剩余大小(G)"        ,
        (total       - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
        round((total - free) / total, 4) * 100 "使用率 %"
FROM
        (
                SELECT
                        tablespace_name,
                        SUM(bytes) free
                FROM
                        dba_free_space
                GROUP BY
                        tablespace_name
        )
        a,
        (
                SELECT
                        tablespace_name,
                        SUM(bytes) total
                FROM
                        dba_data_files
                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;

使用业务用户模拟数据量

删除数据后的表空间利用率截图

表空间回收

查询可以回收表空间的大小

--显示多少就可以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_DATA.DBF'  --改为指定的数据文件即可 
ORDER BY   
"smallest(Mb) - HWM" DESC, tablespace_name, file_name;  

以上SQL参考https://eco.dameng.com/community/question/fc30ca06edb7fa993dfcf431d85e8079
通过以上SQL查询出smallest(Mb)，执行以下SQL回收表空间大小

alter tablespace "TB_DATA" resize datafile 'TB_DATA01.DBF' to 5700;

将表空间上所有表删除后，通过表空间利用率查询

通过以上图片可以看出，表空间仅使用了76M，但是可回收的空间为0，通过查询dba_tables，dba_index，DBA_EXTENTS，V$PURGE_PSEG_TAB都无相关表空间被使用的记录，仍然无法再进行回收，期待达梦的功能完善。

将数据mv到新表空间，删除膨胀旧表空间

1）03134284044-20230417-187846-20040及之前的版本没有alter tablespace xxx resize datafile功能，如果想回收表空间，思路是先创建新的表空间，再将表挪到新表空间上，然后将已扩展无法回收的表空间删除，在操作之前要充分考虑到现在磁盘容量问题。
-2）mv表空间时会遇到表有自增列时报错的问题，在03134284094-20230919-202737-20067及之后无需处理，已完善该功能，在之前的版本上MV表空间需要先处理自增列的问题
3）挪动表空间后把用户所属表空间也做相应变更
4）以下内容会分别对两种情况进行介绍

版本情况说明

版本名称：DM8 第三季度季度版
发版日期：2023 年 10 月 19 日
版本号：select * from v$version;
DM Database Server 64 V8
DB Version: 0x7000c
03134284094-20230919-202737-20067

查询表所在的表空间

select * from dba_tables where TABLESPACE_NAME='表空间'

03134284094-20230919-202737-20067版本之前处理方法

--挪表空间
declare 
i int;
tablename varchar;
BEGIN
--i := 0;
for rec in (select owner,OBJECT_NAME from DBA_OBJECTS where owner in (select owner from DBA_TABLES t where t.TABLESPACE_NAME='原有表空间' and t.owner in ('模式名1',
'模式名2')) and DBA_OBJECTS.OBJECT_TYPE = 'TABLE') loop
tablename := rec.OBJECT_NAME;
select count(*) into i from dba_objects a where object_id  in ( select b.id 
from SYS.SYSCOLUMNS a,sysobjects b 
where b.id=a.id  and a.info2='1') and  a.object_name=tablename;--查询是否自增列
if i THEN
execute IMMEDIATE 'SET IDENTITY_INSERT ' || rec.owner ||'.'||tablename ||' on;';
end if;
execute IMMEDIATE 'alter table ' ||rec.owner ||'.'||tablename||' move tablespace "新的表空间名称";';
end loop;
end;

03134284094-20230919-202737-20067版本及之后处理方法

declare 
tablename varchar;
BEGIN
--i := 0;
for rec in (select  owner,OBJECT_NAME from DBA_OBJECTS where owner in (select owner from DBA_TABLES t 
where t.TABLESPACE_NAME='原有表空间名称' ) 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 用户名 DEFAULT TABLESPACE 新表空间;
ALTER USER 用户名 DEFAULT index TABLESPACE 新表空间;

查询表和索引确认没有在旧表空间上后，可对旧表空间进行删除

select * from dba_tables where TABLESPACE_NAME='TB_DATA';
select * from DBA_INDEXES WHERE TABLESPACE_NAME ='TB_DATA';