图灵小队—MES数据库的数据迁移问题分析—后续进行表收缩

图灵小队—MES数据库的数据迁移问题分析

一、问题现象

各工厂定期进行数据迁移，每次迁移数据量比较大。

迁移方式：insert然后delete。

迁移脚本：https://www.topunix.com/wp-content/uploads/2022/03/PROC_BK_MES_BACKUPS.txt

二、问题分析

这种迁移方式会造成很多的问题。

1、delete数据后，段空间不会收缩

（1）查看表空间使用率
（2）查看段数量
（3）清理后查看占用空间

2、delete操作，不会释放表空间

（1）插入数据5000条，查看段大小

begin
   for i in 1 .. 5000 loop
     insert into test.test01 values(2,'TLTD');
    commit;
  end loop;
end;
/

SQL> select owner,segment_name,segment_type,tablespace_name,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name='TEST01';

OWNER	SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME   HEADER_FILE	HEADER_BLOCK	   BYTES	BLOCKS	EXTENTS
-----   -----------   ---------------  ----------------    -----------   --------------   --------     -------  -------
TEST	TEST01	           TABLE	TEST_TBSP1	    9	          130	            131072	  16	   2

（2）插入数据5000条，再次查看段大小

begin
   for i in 1 .. 5000 loop
   insert into test.test01 values(3,'TTDD');
    commit;
  end loop;
end;
/

SQL> select owner,segment_name,segment_type,tablespace_name,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name='TEST01';
OWNER  SEGMENT_NAME	SEGMENT_TYPE	  TABLESPACE_NAME    HEADER_FILE  HEADER_BLOCK      BYTES      BLOCKS	   EXTENTS
----- -----------     ---------------      ----------------  ----------  --------------    --------    -------   --------
TEST	TEST01	           TABLE	      TEST_TBSP1	 9	    130	            196608         24	     3

（3）删除数据，再次查看段大小（数据段并没有回收）

SQL>delete test.test01 where id=3;
5000 rows deleted.
SQL> select owner,segment_name,segment_type,tablespace_name,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name='TEST01';

OWNER	SEGMENT_NAME	 SEGMENT_TYPE	TABLESPACE_NAME    HEADER_FILE	  HEADER_BLOCK	BYTES	BLOCKS   EXTENTS
-----   -----------    --------------- ----------------   -----------    -------------- -------- ------- ----------
TEST	  TEST01	   TABLE	    TEST_TBSP1	       9	       130	196608	   24	    3

（4）truncate表，再次查看段大小（数据段已经进行了回收，变为了初始大小）

SQL>truncate table test.test01;

SQL> select owner,segment_name,segment_type,tablespace_name,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name='TEST01'; 

OWNER	SEGMENT_NAME	SEGMENT_TYPE    TABLESPACE_NAME  HEADER_FILE  HEADER_BLOCK    BYTES      BLOCKS     EXTENTS
-----    -----------    --------------- ---------------- ----------- -------------- --------     -------    -------  
TEST	  TEST01	  TABLE           TEST_TBSP1         9           130          65536         8          1

3、收缩表

（1）开启行迁移

SQL>alter table test.test01 enable row movement;

（2）收缩表数据

SQL>alter table test.test01 shrink space;

（3）查看数据

SQL> select owner,segment_name,segment_type,tablespace_name,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name='TEST01';

OWNER	SEGMENT_NAME	SEGMENT_TYPE    TABLESPACE_NAME  HEADER_FILE  HEADER_BLOCK    BYTES      BLOCKS     EXTENTS
-----    -----------    --------------- ---------------- ----------- -------------- --------     -------    -------  
TEST       TEST01             TABLE           TEST_TBSP1      9           130         131072        16         2

（4）关闭行迁移

SQL>alter table test.test01 disable row movement;

三、解决方案

1、查看数据迁移脚本

https://www.topunix.com/wp-content/uploads/2022/03/PROC_BK_MES_BACKUPS.txt

2、根据脚本获取delete的表

SELECT count(*) FROM mes_bz.BK_ERROR_BANHAO;
SELECT count(*) FROM mes_bz.MES_SO_PROCESSFLOW;
SELECT count(*) FROM mes_bz.MES_SO_PFITEM;
SELECT count(*) FROM mes_bz.MES_SO_PFITEMREL;
SELECT count(*) FROM mes_bz.MES_PLAN_SOPFITEM;
SELECT count(*) FROM mes_bz.MES_SO_PFGXMAT;
SELECT count(*) FROM mes_bz.MES_SO_PFGXMAT_R;
SELECT count(*) FROM mes_bz.MES_PRD_JIGONG;
SELECT count(*) FROM mes_bz.MES_SO_PFITEMSEC;
SELECT count(*) FROM mes_bz.MES_PRDSUBMIT_HIS;
SELECT count(*) FROM mes_bz.MES_PRDSUBMIT;
SELECT count(*) FROM mes_bz.MES_PRDSUBMIT_ITEM;
SELECT count(*) FROM mes_bz.MES_PRD_PARA;
SELECT count(*) FROM mes_bz.MES_DZ_STATUS_HIS;
SELECT count(*) FROM mes_bz.MES_PZAUTOASSIGNWORK;
SELECT count(*) FROM mes_bz.NEIP_LOG;
SELECT count(*) FROM mes_bz.NEIP_TASK_HISTORY;
SELECT count(*) FROM mes_bz.SYS_LOG;
SELECT count(*) FROM mes_bz.MES_JMT_TRANSPORTCOST;
SELECT count(*) FROM mes_bz.MES_JMT_DELIVERSHEETS;
SELECT count(*) FROM mes_bz.MES_JMT_VOUCHERITEM;
SELECT count(*) FROM mes_bz.MES_JMT_MATERIALDETAILS;
SELECT count(*) FROM mes_bz.MES_JMT_CUSITEM;
SELECT count(*) FROM mes_bz.MES_JMT_SEND;
SELECT count(*) FROM mes_bz.MES_DC_WASTE_DEPOT;
SELECT count(*) FROM mes_bz.MES_JMT_PROCESSCOST;
SELECT count(*) FROM mes_bz.MES_JMT_BACKWAREHOUSE;
SELECT count(*) FROM mes_bz.MES_PCDEPT_DAYWORKTIME;
SELECT count(*) FROM mes_bz.MES_JMT_DAYWORKTIME;
SELECT count(*) FROM mes_bz.MES_INTEGRATEDSERVICE;
SELECT count(*) FROM mes_bz.MES_PLAN_STATELOCK;
SELECT count(*) FROM mes_bz.MES_DC_ZHUANGBAN;
SELECT count(*) FROM mes_bz.MES_DC_ZB_BANGUN;
SELECT count(*) FROM mes_bz.MES_DC_ZB_JIGONG;

3、查看上述所有表的段大小及所占空间

SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='FROM BK_ERROR_BANHAO';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_SO_PROCESSFLOW';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_SO_PFITEM';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_SO_PFITEMREL';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PLAN_SOPFITEM';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_SO_PFGXMAT';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_SO_PFGXMAT_R';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PRD_JIGONG';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_SO_PFITEMSEC';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PRDSUBMIT_HIS';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PRDSUBMIT';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PRDSUBMIT_ITEM';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PRD_PARA';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_DZ_STATUS_HIS';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PZAUTOASSIGNWORK';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='NEIP_LOG';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='NEIP_TASK_HISTORY';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='SYS_LOG';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_TRANSPORTCOST';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_DELIVERSHEETS';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_VOUCHERITEM';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_MATERIALDETAIL';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_CUSITEM';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_SEND';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_DC_WASTE_DEPOT';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_PROCESSCOST';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_BACKWAREHOUSE';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PCDEPT_DAYWORKTIME';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_JMT_DAYWORKTIME';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_INTEGRATEDSERVICE';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_PLAN_STATELOCK';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_DC_ZHUANGBAN';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_DC_ZB_BANGUN';
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='MES_DC_ZB_JIGONG';

4、对表进行统计信息收集。

analyze table ITPUX_M10 delete statistics;

begin
  dbms_stats.gather_table_stats(ownname => 'TEST2',
    tabname => 'TEST2_M10',
    estimate_percent =>
    dbms_stats.AUTO_SAMPLE_SIZE,
    method_opt => 'for all indexed columns',
    cascade => true,
    degree => 2);
end;
/

5、查看运安实际数据量和看到的数据文件比较

select count(*) from mes_bz.MES_SO_PFGXMAT;
select * from DBA_TAB_MODIFICATIONS where table_owner='MES_BZ' order by timestamp desc;
select owner,table_name,stattype_locked from dba_tab_statistics where owner='MES_BZ';
select owner,table_name,tablespace_Name,num_rows,blocks,blocks*8*1024/1024/1024/1024 gb from dba_tables where blocks is not null order by gb desc;
select sum(blocks*8*1024/1024/1024/1024) gb from dba_tables where num_rows is not null order by num_rows desc;
select sum(bytes/1024/1024/1024) gb from dba_data_files;

（1）查看dba_segements;

（2）查看信息统计出来的实际占用空间

（3）系统中数据文件占用的数据（388G）

（4）比较2和3，发现实际数据和数据文件显示的有较大的差别

最大原因时，数据迁移过程，delete的数据表，虽然数据被清理了，但是空间没有被释放。

6、解决方案—收缩表（收缩表的原则，从小表到大表-防止收缩过程，大表过慢，导致数据库卡死）

（1）开启行迁移

SQL>alter table test.test01 enable row movement;

（2）收缩表数据

SQL>alter table test.test01 shrink space;

（3）查看数据

SQL> select owner,segment_name,segment_type,tablespace_name,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name='TEST01';

OWNER	SEGMENT_NAME	SEGMENT_TYPE    TABLESPACE_NAME  HEADER_FILE  HEADER_BLOCK    BYTES      BLOCKS     EXTENTS
-----    -----------    --------------- ---------------- ----------- -------------- --------     -------    -------  
TEST       TEST01             TABLE           TEST_TBSP1      9           130         131072        16         2

（4）关闭行迁移

SQL>alter table test.test01 disable row movement;