达梦表空间占用过高问题处理
背景需求
近期项目中有一个将dsc集群的备份拷贝到主备集群做还原，还原之后，磁盘空间占用非常高，触发告警
查看其中一个表空间，含有3个数据文件，每个数据文件都达到47G，使用率达到99%，无法对表空间缩容。
以用户的方式导出数据，然后将用户和表空间删除重建，将数据导入进去之后，表空间使用率降到57%
本地进行相关测试
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;
如下图所示：
查看用户下对象所占空间大小
select segment_name,segment_type,tablespace_name,owner,bytes/1024/1024 MB from dba_segments where owner='DMTEST'；
先删除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;
--执行之后，占用空间如下：
将用户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 &
导出成功之后看下表空间使用率及数据库对象所占大小
而通过重建用户和表空间之后重新释放了空闲的磁盘空间，特别是对一些老版本，用户表空间过大无法缩小，可以通过该方法释放空间
