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

注：收缩表空间，如果空闲空间都在尾部，可以直接收缩成功，如果尾部不空闲，中部空闲，则需要移走使用尾部的表后再收缩，生产环境，如果需要移动表才能收缩，那么不要在业务使用时操作，可能会严重影响业务系统使用，特别是移动大表。
1.1查询表空间情况
SELECT TOTAL_SIZEPAGE_SIZE/1024/1024,FREE_SIZEPAGE_SIZE/1024/1024,PATH,CLIENT_PATH FROM V$DATAFILE;

SELECT path,free_page_no1.0page()/1024/1024 FROM SYS.V$DATAFILE
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_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 2 desc;

1.2收缩表空间

ALTER tablespace TEST RESIZE DATAFILE ‘/data/dmdata/DAMENG/BOOKSHOP.DBF’ to xxx;
如果报错无法回收簇，是因为表空间尾部有有效数据，如下示例

1.3查询目标表空间数据文件(示例main表空间)尾部的表

可以逐步重复查询，移动表，尝试收缩操作。
select distinct
o.owner,
e.USED,
e.extent_id,
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

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.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_COUNT
FROM DBA_TABLES A
WHERE 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_NAME=‘TBS_TEMP_YD’;
SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAME=‘TBS_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)对表t1，t2，t3，t4分别插入测试

DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 1000000 LOOP
insert 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_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 2 desc;
SELECT path,free_page_no1.0page()/1024/1024 FROM SYS.V$DATAFILE;

SELECT TOTAL_SIZEPAGE_SIZE/1024/1024,FREE_SIZEPAGE_SIZE/1024/1024,PATH,
CLIENT_PATH FROM V$DATAFILE;

(4)查询表空间尾部表，删除部分测试表数据，制造中部空闲空间

select distinct
o.owner,
e.USED,
e.extent_id,
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 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;
–asc,查询前端表对象

–desc,查询出尾部使用空间是表t3

明细步骤
–查询尾部对象

SELECT * FROM v$extents

WHERE ts_id=(select id from v$tablespace where name=‘MAIN’) 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_name=‘INDEX33555494’;

–truncate删除数据，查询空间情况
truncate table t2;
truncate table t4;

(4)尝试收缩

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-324=176M,收缩报错是因为main表空间数据文件尾部有使用。

(5)新建自定义表空间TBS_TEMP_YD，并移动t1，t3表到表空间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_COUNT
FROM DBA_TABLES A
WHERE A.OWNER IN (‘SYSDBA’) AND TABLE_NAME=‘T3’;
alter table sysdba.t3 MOVE TABLESPACE TBS_TEMP_YD;
alter table sysdba.t1 MOVE TABLESPACE TBS_TEMP_YD;
SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME=‘TBS_TEMP_YD’;
SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAME =‘TBS_TEMP_YD’;

(5)再次尝试收缩，收缩成功
ALTER TABLESPACE MAIN RESIZE DATAFILE ‘MAIN.DBF’ TO xxx;