达梦表空间占用过高问题处理
背景需求
近期项目中有一个将dsc集群的备份拷贝到主备集群做还原,还原之后,磁盘空间占用非常高,触发告警
查看其中一个表空间,含有3个数据文件,每个数据文件都达到47G,使用率达到99%,无法对表空间缩容。
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155734526-1835466304.png)
以用户的方式导出数据,然后将用户和表空间删除重建,将数据导入进去之后,表空间使用率降到57%
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155734686-1951172717.png)
本地进行相关测试
drop table if exists emp;
create table emp (eid int primary key identity(1,1),ename varchar(200),age int,hiredate date,deptno int);
create index idx_emp_name on emp(ename);
create index idx_emp_age on emp (age);
declare i int;
begin
for i in 1..5000000 loop
insert into emp (ename,age,hiredate,deptno)
select dbms_random.string('2',trunc(dbms_random.value(2,8))),
trunc(dbms_random.value(1,100)),
ADD_DAYS(sysdate(),dbms_random.value(-10000,-10)),
trunc(dbms_random.value(1,6)) from dual;
end loop;
if mod(i,5000)=0 then
commit;
end if;
end;
查看表空间
SELECT SF_GET_PARA_STRING_VALUE(1,'INSTANCE_NAME') AS 实例名, u.USERNAME as 用户名,A.NAME AS 表空间名,B.PATH AS 数据文件路径,(B.TOTAL_SIZE*PAGE/1024/1024)AS 文件大小MB,(B.FREE_SIZE*PAGE/1024/1024)AS 剩余大小MB,(CAST((B.TOTAL_SIZE-B.FREE_SIZE)*100/B.TOTAL_SIZE AS NUMERIC(2,0))||'%') AS 使用率,DECODE(B.AUTO_EXTEND,'0','关闭','1','打开') AS 自动扩展,B.NEXT_SIZE AS 扩展大小MB FROM V$TABLESPACE A,V$DATAFILE B,DBA_USERS u WHERE A.ID = B.GROUP_ID and u.PROFILE=B.path;
如下图所示:
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155731379-773954867.png)
查看用户下对象所占空间大小
select segment_name,segment_type,tablespace_name,owner,bytes/1024/1024 MB from dba_segments where owner='DMTEST';
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155730305-84573153.png)
先删除1000000条数据,然后新增10000000条数据,最后删除到只留下前20000条数据
delete from emp where eid<1000000;
commit;
declare i int;
begin
for i in 1..1000000 loop
insert into emp (ename,age,hiredate,deptno)
select dbms_random.string('2',trunc(dbms_random.value(2,8))),
trunc(dbms_random.value(1,100)),
ADD_DAYS(sysdate(),dbms_random.value(-10000,-10)),
trunc(dbms_random.value(1,6)) from dual;
end loop;
if mod(i,5000)=0 then
commit;
end if;
end;
delete from emp where eid>20000;
commit;
--执行之后,占用空间如下:
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155731721-1561051785.png)
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155730249-1513316520.png)
将用户DMTEST导出
nohup dexp USERID=SYSDBA/SYSDBA FILE=/dmdata/dmtest.dmp OWNER=dmtest COMPRESS_LEVEL=2 PARALLEL=2 >> dmtest_dexp.log &
删除用户和表空间,然后重建
drop user "DMTEST" cascade;
drop tablespace "TBS_DMTEST";
--创建表TBS_DMTEST表空间,数据文件为TBS_TEST1.DBF,初始大小为512M,自动扩充,每次扩充5M,最大扩充上限20480M
create tablespace "TBS_DMTEST" datafile 'TBS_TEST1.DBF' size 512 autoextend on next 5 maxsize 20480 CACHE = NORMAL;
--创建DMTEST用户,所属表空间为TBS_DMTEST
create user "DMTEST" identified by "dameng123" default tablespace "TBS_DMTEST" default index tablespace "TBS_DMTEST";
--给用户授权以下角色权限
grant "DBA","PUBLIC","RESOURCE","SOI","SVI","VTI" to "DMTEST";
导入数据到重新创建的用户里面
nohup dimp SYSDBA/SYSDBA FILE=/dmdata/dmtest.dmp OWNER=DMTEST PARALLEL=2 TABLE_PARALLEL=2 FAST_LOAD=y LOB_NOT_FAST_LOAD=y FEEDBACK=100000 >> dmtest_dimp.log &
导出成功之后看下表空间使用率及数据库对象所占大小
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155732534-266426066.png)
![](https://img2024.cnblogs.com/blog/2164568/202402/2164568-20240201155730645-1181353214.png)
而通过重建用户和表空间之后重新释放了空闲的磁盘空间,特别是对一些老版本,用户表空间过大无法缩小,可以通过该方法释放空间