达梦数据迁移工具DTS使用实践

1、环境描述

2、DTS概述

1.支持视图、存储过程/函数、包、类、同义词、触发器等对象迁移；
2.支持数据类型的自动映射，编码转换；
3.支持根据条件自定义迁移部分数据；
4.向导式迁移步骤，上手简单；
5.支持 web 端操作、监控；
6.支持迁移评估。

3、DTS典型应用场景

1.支持全量静态数据迁移，无法实现数据增量迁移方式；
2.为保障迁移全量数据的一致性，需要充足的业务系统停机窗口；
3.迁移过程中，源端数据库不能有数据变更以及对象变更。

4、数据库调研

4.1源端Oracle数据库调研

4.1.1迁移对象统计

select a.username, (select count(1) from dba_tables b where b.owner = a.username) table_num,
        ( SELECT COUNT(1) FROM DBA_INDEXES I WHERE UNIQUENESS = 'UNIQUE' AND OWNER =A.USERNAME OR INDEX_NAME NOT LIKE 'SYS_%' AND OWNER =A.USERNAME) index_num,
        (select count(distinct c.table_name)
           from dba_tab_partitions c
          where c.table_owner = a.username) part_num,
        (select count(1)
           from dba_tab_cols d
          where d.OWNER = a.username
            and d.DATA_TYPE like '%LOB%')  lob_num,
        (select sum(e.bytes) / 1024 / 1024 / 1024
           from dba_extents e
          where exists (select 1
                   from dba_lobs f
                  where f.owner = a.username
                    and f.segment_name = e.segment_name)) lob_space,
        (select count(1) from dba_views g where g.OWNER = a.username) view_num,
        (select count(1) from dba_triggers h where h.owner = a.username) trig_num,
        (select count(DISTINCT I.NAME)
           from DBA_SOURCE I
          WHERE I.OWNER = A.username
            AND I.TYPE = 'FUNCTION') fun_num,
        (select COUNT(1)
           FROM DBA_SEQUENCES j
          WHERE j.sequence_owner = A.username) seq_num,
        (select count(1) from dba_synonyms where owner= A.username) syn,
        (select COUNT(1) FROM DBA_MVIEWS K WHERE K.owner = A.username) mv_num,
        (select count(DISTINCT l.NAME)
           from DBA_SOURCE L
          WHERE L.OWNER = A.username
            AND L.TYPE = 'PROCEDURE') stor_num,
        (select COUNT(1) FROM DBA_DB_LINKS M WHERE M.owner = A.username) dblink_num,
        (select max(n.DATA_LENGTH)
           from dba_tab_cols n
          where n.OWNER = a.username) max_length,
        (select SUM(O.DATA_LENGTH)
           from dba_tab_cols o
          where o.OWNER = a.username
            and o.DATA_TYPE not like '%LOB%') max_row_wide
   from dba_users a where username in ('HR');

4.1.2迁移数据量统计

select distinct segment_type,sum(BYTES)/1024 /1024 /1024 , COUNT(*) 
FROM DBA_SEGMENTS 
where owner = 'HR' group by segment_type order by 2 desc;

4.1.3字符集信息统计