move tablespace 方式收缩Oracle表碎片

精选原创

食品安全辛吉飞 2024-05-09 13:41:31 博主文章分类：Oracle ©著作权

©著作权归作者所有：来自51CTO博客作者食品安全辛吉飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

解决方案

move tablespace概要说明：
move tablespace 操作可以收缩段、消除部分行迁移、消除空间碎片、使数据更紧密。move tablespace 操作会降低高水位线，但不会释放申请的空间。
可以使用move将一个表从当前的tablespace中移动到另一个tablespace中，或者仅在当前的tablespace中移动。
move操作时，会在表上添加exclusive lock，此时不允许其它DML操作，仅可以查询。
move操作时，需要目标tablespace中有1倍于表大小的空闲空间以供使用。
move操作后，数据的rowid发生了改变，所以表上的index是必须要rebuild的。
本案例中数据库版本为11.2.0.4，只在表的当前表空间中进行move 操作,本次不涉及lob字段。

一、普通表

1、查看使用指定表空间的用户及数据量

select owner,sum(bytes/1024/1024/1024) gb from dba_segments
where tablespace_name='DATA_TBS'
group by owner
order by gb desc;

2、创建表名及表实际大小信息相关表

创建存放表名信息的表

create table system.TEMP_EDW_SEGMENTS
(
 SEGMENT_NAME VARCHAR2(40),
 OWNER VARCHAR2(20)
);

创建数据大小信息表，REAL_SIZE_MB 表示实际数据大小，SEG_SIZE_MB表示数据段大小
SEG_SIZE_MB 减去 REAL_SIZE_MB ，近似于碎片大小，单位 MB

create table system.FRAG_RESAULTS
(
 OWNER VARCHAR2(20),
 TNAME VARCHAR2(40),
 REAL_SIZE_MB NUMBER,
 SEG_SIZE_MB NUMBER
);

3、向表名信息表中插入数据

本次统计超过1GB 的表

insert into system.temp_edw_segments
select segment_name,owner from dba_segments
where segment_type='TABLE'
and owner='EDW'
and TABLESPACE_NAME='DATA_TBS'
and (bytes/1024/1024/1024)>1;

commit;

4、创建计算表或分区实际大小的函数

该函数可以指定四个参数，分别是数据段名称、用户名、数据段类型、分区名
其返回结果为表或分区实际的大小，单位为bytes

CREATE OR REPLACE FUNCTION REAL_SIZE(
P_SEGNAME IN VARCHAR2,
P_OWNER IN VARCHAR2 DEFAULT USER,
P_TYPE IN VARCHAR2 DEFAULT 'TABLE',
P_PART_NAME IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER AUTHID CURRENT_USER AS
L_TOTAL_BLOCKS NUMBER;
L_TOTAL_BYTES NUMBER;
L_UNUSED_BLOCKS NUMBER;
L_UNUSED_BYTES NUMBER;
L_LASTUSEDEXTFILEID NUMBER;
L_LASTUSEDEXTBLOCKID NUMBER;
L_LAST_USED_BLOCK NUMBER;
L_UNFORMATTED_BLOCKS NUMBER;
L_UNFORMATTED_BYTES NUMBER;
L_FS1_BLOCKS NUMBER;
L_FS1_BYTES NUMBER;
L_FS2_BLOCKS NUMBER;
L_FS2_BYTES NUMBER;
L_FS3_BLOCKS NUMBER;
L_FS3_BYTES NUMBER;
L_FS4_BLOCKS NUMBER;
L_FS4_BYTES NUMBER;
L_FULL_BLOCKS NUMBER;
L_FULL_BYTES NUMBER;
T_TOTAL_BYTES NUMBER;
T_FS_BYTES NUMBER; 
BEGIN
DBMS_SPACE.SPACE_USAGE(
P_OWNER,
P_SEGNAME,
P_TYPE,
L_UNFORMATTED_BLOCKS,
L_UNFORMATTED_BYTES,
L_FS1_BLOCKS,
L_FS1_BYTES,
L_FS2_BLOCKS,
L_FS2_BYTES,
L_FS3_BLOCKS,
L_FS3_BYTES,
L_FS4_BLOCKS,
L_FS4_BYTES,
L_FULL_BLOCKS,
L_FULL_BYTES,
P_PART_NAME);
DBMS_SPACE.UNUSED_SPACE(
P_OWNER,
P_SEGNAME,
P_TYPE,
L_TOTAL_BLOCKS,
L_TOTAL_BYTES,
L_UNUSED_BLOCKS,
L_UNUSED_BYTES,
L_LASTUSEDEXTFILEID,
L_LASTUSEDEXTBLOCKID,
L_LAST_USED_BLOCK,
P_PART_NAME);
T_FS_BYTES := L_FS1_BYTES * 0.25 / 2 + L_FS2_BYTES * (0.5 + 0.25) / 2 +L_FS3_BYTES * (0.75 + 0.5) / 2 +L_FS4_BYTES * (1 + 0.75) / 2 + L_UNUSED_BYTES;
T_TOTAL_BYTES := L_TOTAL_BYTES;
RETURN T_TOTAL_BYTES-T_FS_BYTES;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END;
/

5、查询表碎片信息

数据段大小减去实际数据大小，近似于碎片大小

--查询指定表数据段大小
select bytes/1024/1024 seg_size_mb from dba_segments where owner='EDW' and segment_name='EMPLOYEES';
--查询指定表实际数据大小
select trunc(real_size('EMPLOYEES','EDW')/1024/1024, 2) real_size_mb from dual;

编写查询脚本，依次查询system.temp_edw_segments中所有表的实际数据大小信息，并将结果写入到system.FRAG_RESAULTS 表

spool suipian.log;
set serveroutput on;
declare
cursor c1 is select * from system.temp_edw_segments;
v_sql long;
begin
for i in c1 loop
v_sql := 'insert into system.frag_resaults
select owner,segment_name,
trunc(real_size('''||i.segment_name||''','''||i.owner||''')/1024/1024, 2) real_size_mb,
bytes/1024/1024 seg_size_mb
from dba_segments s where s.owner='''||i.owner||''' and s.segment_name='''||i.segment_name||'''';
execute immediate v_sql;
dbms_output.put_line(v_sql);
commit;
end loop;
end;
/
spool off;
exit;

导出system.FRAG_RESAULTS 表数据，对其中碎片较大的表进行move tablespace 操作，用以收缩表碎片。

6、生成移动表及重建索引的语句

生成移动表的语句

select 'ALTER TABLE '||owner||'.'||table_name||' move parallel 8 nologging;' sqltext
from dba_tables where table_name in
( 
'DEPARTMENTS',
'REGIONS',
'LOCATIONS',
'JOBS',
'EMPLOYEES',
'JOB_HISTORY'
) and owner='EDW';

生成rebuild 索引的语句

set lines 300;
set pages 300;
select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD  online parallel 8 nologging;' sqltext
from dba_indexes 
where table_name in(   
'DEPARTMENTS',
'REGIONS',
'LOCATIONS',
'JOBS',
'EMPLOYEES',
'JOB_HISTORY'
) and owner='EDW';

执行上述拼接出来的语句，或者写入脚本统一执行。

7、检查表及索引状态

检查表的状态

select object_name,object_type,status from dba_objects where object_name 
in(
'DEPARTMENTS',
'REGIONS',
'LOCATIONS',
'JOBS',
'EMPLOYEES',
'JOB_HISTORY'
) AND OWNER='EDW';

检查索引的状态

select table_name,index_name,status from dba_indexes 
where table_name in
(
'DEPARTMENTS',
'REGIONS',
'LOCATIONS',
'JOBS',
'EMPLOYEES',
'JOB_HISTORY'
) AND OWNER='EDW'
and status<>'VALID';

8、修改表和索引的并行

表的并行

select 'alter table '||owner||'.'||table_name||' parallel 1;' sqltext
from dba_tables where table_name in
(
'DEPARTMENTS',
'REGIONS',
'LOCATIONS',
'JOBS',
'EMPLOYEES',
'JOB_HISTORY'
) and owner='EDW';

索引的并行

select 'ALTER INDEX '||owner||'.'||index_name||' noparallel;' sqltext
from dba_indexes where table_name in
(
'DEPARTMENTS',
'REGIONS',
'LOCATIONS',
'JOBS',
'EMPLOYEES',
'JOB_HISTORY'
) and owner='EDW';

执行上述拼接出来的语句，或者写入脚本统一执行。

9、收集统计信息

exec dbms_stats.gather_table_stats(ownname => 'EDW',tabname => 'EMPLOYEES',estimate_percent => 10,method_opt=> 'FOR ALL COLUMNS SIZE AUTO',degree => '8',cascade=>TRUE);

二、分区表

1、查看使用指定表空间的用户及数据量

select owner,sum(bytes/1024/1024/1024) gb from dba_segments
where tablespace_name='DATA_TBS_HIS'
group by owner
order by gb desc;

2、创建表名及表实际大小信息相关表

创建存放表名信息的表

create table system.TEMP_PART_EDW_SEGMENTS
(
 SEGMENT_NAME VARCHAR2(40),
 OWNER VARCHAR2(20),
 PARTITION_NAME VARCHAR2(30)
);

创建数据大小信息表，REAL_SIZE_MB 表示实际数据大小，SEG_SIZE_MB表示数据段大小
PNAME表示分区名，SEG_SIZE_MB 减去 REAL_SIZE_MB ，近似于碎片大小，单位 MB

create table system.FRAG_PART_RESAULTS
(
 OWNER VARCHAR2(20),
 TNAME VARCHAR2(40),
 PNAME VARCHAR2(30),
 REAL_SIZE_MB NUMBER,
 SEG_SIZE_MB NUMBER
);

3、向表名信息表中插入数据

本次统计超过1GB 的分区

insert into system.temp_part_edw_segments
select segment_name,owner,PARTITION_NAME from dba_segments
where segment_type='TABLE PARTITION'
and owner='EDW'
and TABLESPACE_NAME='DATA_TBS_HIS'
and (bytes/1024/1024/1024)>1;

commit;

4、查询表碎片信息

数据段大小减去实际数据大小，近似于碎片大小

--查询指定分区数据段大小
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,bytes/1024/1024 mb from dba_segments
where segment_type='TABLE PARTITION'
and owner='EDW'
and TABLESPACE_NAME='DATA_TBS_HIS'
and segment_name='TABLE_HIS'
and PARTITION_NAME='P_202312';

--查询指定分区实际数据大小
select trunc(real_size('TABLE_HIS','EDW','TABLE PARTITION','P_202312')/1024/1024, 2) real_size_mb from dual;

编写查询脚本，依次查询system.temp_part_edw_segments中所有表的实际数据大小信息，并将结果写入到system.FRAG_PART_RESAULTS 表

spool part_suipian.log;
set serveroutput on;
declare
cursor c1 is select * from system.temp_part_edw_segments;
v_sql long;
begin
for i in c1 loop
v_sql := 'insert into system.frag_part_resaults
select owner,segment_name,partition_name,
trunc(real_size('''||i.segment_name||''','''||i.owner||''',''TABLE PARTITION'','''||i.partition_name||''')/1024/1024, 2) real_size_mb,
bytes/1024/1024 seg_size_mb
from dba_segments s where s.owner='''||i.owner||''' and s.segment_name='''||i.segment_name||''' and s.partition_name='''||i.partition_name||'''';
execute immediate v_sql;
dbms_output.put_line(v_sql);
commit;
end loop;
end;
/
spool off;
exit;

导出system.FRAG_PART_RESAULTS 表数据，对其中碎片较大的表进行move tablespace 操作，用以收缩表碎片。

5、生成移动分区及重建分区索引的语句

生成移动分区的语句

select 'ALTER TABLE '||table_owner ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE DATA_TBS_HIS;' sqltext
from dba_tab_partitions 
where table_owner='EDW'
and table_name='TABLE_HIS'
and PARTITION_NAME='P_202312';

执行拼接出来的语句，然后再进行后续操作
生成rebuild 分区索引的语句

select 'ALTER INDEX '||index_owner ||'.'||index_name||' rebuild partition '||partition_name||' TABLESPACE DATA_TBS_HIS;' sqltext
from dba_ind_partitions where status='UNUSABLE';

6、检查表及索引状态

检查表的状态

select object_name,object_type,status from dba_objects where object_name 
in(
'TABLE_HIS'
) and owner='EDW';

检查索引的状态

select index_owner,index_name,partition_name,status 
from dba_ind_partitions a where index_name in
(select index_name from dba_part_indexes where table_name='TABLE_HIS')
and index_owner='EDW'
and status<>'USABLE';