达梦数据库-收缩数据库表空间步骤及示例记录总结

1达梦数据库-收缩数据库表空间步骤及示例记录总结注收缩表空间如果空闲空间都在尾部可以直接收缩成功如果尾部不空闲中部空闲则需要移走使用尾部的表后再收缩生产环境如果需要移动表才能收缩那么不要在业务使用时操作可能会严重影响业务系统使用特别是移动大表。1.1查询表空间情况SELECT TOTAL_SIZE*PAGE_SIZE/1024/1024,FREE_SIZE*PAGE_SIZE/1024/1024,PATH,CLIENT_PATH FROM V$DATAFILE;SELECT path,free_page_no*1.0*page()/1024/1024 FROM SYS.V$DATAFILESELECT Upper(F.TABLESPACE_NAME) 表空间名,D.TOT_GROOTTE_MB 表空间大小(M),D.TOT_GROOTTE_MB - F.TOTAL_BYTES 已使用空间(M),To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), 990.99)|| % 使用比,F.TOTAL_BYTES 空闲空间(M),F.MAX_BYTES 最大块(M)FROM (SELECT TABLESPACE_NAME,Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME F.TABLESPACE_NAMEORDER BY 2 desc;1.2收缩表空间ALTER tablespace TEST RESIZE DATAFILE /data/dmdata/DAMENG/BOOKSHOP.DBF to xxx;如果报错无法回收簇是因为表空间尾部有有效数据如下示例1.3查询目标表空间数据文件(示例main表空间)尾部的表可以逐步重复查询移动表尝试收缩操作。select distincto.owner,e.USED,e.extent_id,case o.object_type when INDEX THEN (select i.table_namefrom DBA_INDEXES iwhere i.INDEX_NAMEo.object_name limit 1)when TABLE THEN o.object_name else null endas table_namefrom v$extents ejoin V$SEGMENT_INFOS son e.seg_ids.seg_idjoin dba_objects oon o.object_ids.obj_idleft join dba_indexes ion o.object_namei.index_namewhere e.ts_id(select t.id from v$tablespace t where nameMAIN)order by e.extent_id desc limit 10;1.4查询表大小和条数--可查询表大小和条数大致预估移动时间SELECT OWNER,TABLE_NAME,TABLE_USED_PAGES(OWNER,TABLE_NAME)*PAGE()/1024.0/1024.0 SIZE_MB,SF_GET_TABLE_COUNT(A.OWNER, A.TABLE_NAME) TAB_COUNTFROM DBA_TABLES AWHERE A.OWNER IN (xx) AND TABLE_NAME表名;1.5新建普通表空间create tablespace TBS_TEMP_YD datafile TBS_TEMP_YD01.DBF size 128 CACHE NORMAL;1.6移动表到新建普通表空间alter table 模式名.表名 MOVE TABLESPACE TBS_TEMP_YD;1.7查已经移动到表空间TBS_TEMP_YD的表和索引SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAMETBS_TEMP_YD;SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAMETBS_TEMP_YD;1.8根据需要临时设置UNDO_RETENTION参数SP_SET_PARA_DOUBLE_VALUE (1,UNDO_RETENTION,90);1.9使用表空间收缩命令进行空间回收--示例收缩MAIN 命令ALTER TABLESPACE MAIN RESIZE DATAFILE MAIN.DBF TO XXMB;1.10操作示例测试环境--DM v8 --03134284368-20260306-316451-20149 Pack62 Kylin 10 x86_64需求示例把用户表从main表空间中移动到新建自定义表空间TEST,并收缩main表空间。操作步骤(1)创建测试表使用默认main表空间create table t1(id int,info VARCHAR2(100));create table t2(id int,info VARCHAR2(100));create table t3(id int,info VARCHAR2(100));create table t4(id int,info VARCHAR2(100));(2)对表t1t2t3t4分别插入测试DECLAREi NUMBER : 1;BEGINWHILE i 1000000 LOOPinsert into 具体表名(ID, info) VALUES(i, 在这里为要插入的测试数据sdsdsdsdsds||i);i : i 1;END LOOP;END;commit;(3)表空间情况查询SELECT Upper(F.TABLESPACE_NAME) 表空间名,D.TOT_GROOTTE_MB 表空间大小(M),D.TOT_GROOTTE_MB - F.TOTAL_BYTES 已使用空间(M),To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), 990.99)|| % 使用比,F.TOTAL_BYTES 空闲空间(M),F.MAX_BYTES 最大块(M)FROM (SELECT TABLESPACE_NAME,Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME F.TABLESPACE_NAME ORDER BY 2 desc;SELECT path,free_page_no*1.0*page()/1024/1024 FROM SYS.V$DATAFILE;SELECT TOTAL_SIZE*PAGE_SIZE/1024/1024,FREE_SIZE*PAGE_SIZE/1024/1024,PATH,CLIENT_PATH FROM V$DATAFILE;(4)查询表空间尾部表删除部分测试表数据制造中部空闲空间select distincto.owner,e.USED,e.extent_id,case o.object_type when INDEX THEN (select i.table_namefrom DBA_INDEXES iwhere i.INDEX_NAMEo.object_name limit 1)when TABLE THEN o.object_name else null endas table_namefrom v$extents ejoin V$SEGMENT_INFOS son e.seg_ids.seg_idjoin dba_objects oon o.object_ids.obj_idleft join dba_indexes ion o.object_namei.index_namewhere e.ts_id(select t.id from v$tablespace t where nameMAIN)order by e.extent_id desc limit 10;--asc,查询--desc,查询出尾部使用空间是表t3详细步骤--查询尾部对象SELECT * FROM v$extentsWHERE ts_id(select id from v$tablespace where nameMAIN) order by extent_id desc limit 5;--通过seg_id查询具体obj_id名称SELECT * FROM V$SEGMENT_INFOS WHERE seg_id IN (2374);--通过obj_id查询对象名称SELECT * FROM dba_objects WHERE OBJECT_ID IN (33555494);--通过INDEX33555494查询所属表名称select * from dba_indexes where index_nameINDEX33555494;--truncate删除数据查询空间情况truncate table t2;truncate table t4;ALTER TABLESPACE MAIN RESIZE DATAFILE MAIN.DBF TO 408;ALTER TABLESPACE MAIN RESIZE DATAFILE MAIN.DBF TO 176;main表空间总大小500M,free_page_no查询408M所以第一次尝试收缩到408M。查询空闲324M,真正使用空间500-324176M,收缩报错是因为main表空间数据文件尾部有使用。(5)新建自定义表空间TBS_TEMP_YD并移动t1t3表到表空间TBS_TEMP_YD。create tablespace TBS_TEMP_YD datafile TBS_TEMP_YD01.DBF size 128 CACHE NORMAL;SELECT OWNER,TABLE_NAME,TABLE_USED_PAGES(OWNER,TABLE_NAME)*PAGE()/1024.0/1024.0 SIZE_MB,SF_GET_TABLE_COUNT(A.OWNER, A.TABLE_NAME) TAB_COUNTFROM DBA_TABLES AWHERE A.OWNER IN (SYSDBA) AND TABLE_NAMET3;alter table sysdba.t3 MOVE TABLESPACE TBS_TEMP_YD;alter table sysdba.t1 MOVE TABLESPACE TBS_TEMP_YD;SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAMETBS_TEMP_YD;SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAME TBS_TEMP_YD;(6)再次尝试收缩成功收缩ALTER TABLESPACE MAIN RESIZE DATAFILE MAIN.DBF TO xxx;更多达梦数据库运维指南、在线文档、相关资料、社区在线提问以及技术分享访问 https://eco.dameng.com/