为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: DM 163
【操作系统】: 海光麒麟
【CPU】:海光麒麟V10
【问题描述】*:
达梦-oracle对象以及表数据量对比
ORACLE
INDEX 216
TABLE PARTITION 152 <-
TRIGGER 1
PROCEDURE 101
FUNCTION 21
INDEX PARTITION 39 <-
SEQUENCE 1
TABLE 547
VIEW 10
DM
1 SCH 4
2 PROCEDURE 100
3 FUNCTION 21
4 SEQUENCE 1
5 VIEW 10
6 TABLE 547
7 TRIGGER 1
8 TABLE PARTITION 152
9 INDEX 1067 <-
10 CONSTRAINT 74
达梦对比于oracle新增信息:SCH,CONSTRAINT
以下为oracle-dm变化:
达梦 PROCEDURE:101
oracle PROCEDURE:101
达梦 INDEX:1067 ->
oracle INDEX PARTITION:39
达梦索引多了5倍左右
可以使用以下sql查询源端和目的端的具体索引信息
SELECT OWNER,INDEX_NAME,TO_CHAR(DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER))
from SYS.DBA_INDEXES WHERE
owner NOT IN ('SYSDBA','SYSAUDITOR','SYSSSO','SYS','SYSJOB');
因为DM 默认的表是索引组织表,而Oracle 默认是堆表,迁移后到达梦每个表都会多INDEX开头的索引,用一下sql可以统计排除达梦自身创建的索引(后面用户名自定义更改)
SELECT
A.USERNAME "用户名",
(SELECT COUNT(1) FROM DBA_TABLES B WHERE B.OWNER = A.USERNAME) "表数量",
( SELECT COUNT(1) FROM DBA_VIEWS G WHERE G.OWNER = A.USERNAME ) "视图数量",
( SELECT COUNT(1) FROM DBA_TRIGGERS H WHERE H.OWNER = A.USERNAME ) "触发器数量",
( SELECT COUNT(DISTINCT I.NAME) FROM DBA_SOURCE I WHERE I.OWNER = A.USERNAME AND I.TYPE = 'FUNCTION' ) "函数数量",
( SELECT COUNT(1) FROM DBA_SEQUENCES J WHERE J.SEQUENCE_OWNER = A.USERNAME ) "序列数量",
( SELECT COUNT(DISTINCT L.NAME) FROM DBA_SOURCE L WHERE L.OWNER = A.USERNAME AND L.TYPE = 'PROCEDURE' ) "存储过程数量",
( SELECT COUNT(1) FROM DBA_DB_LINKS M WHERE M.OWNER = A.USERNAME ) "DBLINK数量",
( SELECT COUNT(1) FROM DBA_INDEXES I WHERE UNIQUENESS = 'UNIQUE' AND OWNER =A.USERNAME OR INDEX_NAME NOT LIKE 'INDEX335%' AND OWNER =A.USERNAME) "索引数量",
( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='TYPE' AND OWNER =A.USERNAME OR OBJECT_TYPE='CLASS' AND OWNER =A.USERNAME ) "自定义类型",
( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND OWNER =A.USERNAME) "PKG数量"
FROM
DBA_USERS A WHERE A.USERNAME IN ('SYSDBA');
针对这种数据量不匹配的问题:在 DTS 迁移工具中指定源 oracle 数据库的驱动为自动义的且是与 oracle 版本适配的 jdk 版本,因为默认 dts 工具在选择源端驱动时时默认的一个驱动版本,不一定和真实的库完全匹配。
oracle数据库与JDK的版本选择参考如下:
21.1—— ojdbc11.jar with JDK11, JDK12, JDK13, JDK14 and JDK15;ojdbc8.jar with JDK8, JDK11, JDK12, JDK13, JDK14 and JDK15
19.x——ojdbc10.jar with JDK10, JDK11;ojdbc8.jar with JDK8, JDK9, JDK11
18.3——ojdbc8.jar with JDK8, JDK9, JDK10, JDK11
12.2 or 12cR2——ojdbc8.jar with JDK 8
12.1 or 12cR1——ojdbc7.jar with JDK 7 and JDK 8;ojdbc6.jar with JDK 6
11.2 or 11gR2——ojdbc6.jar with JDK 6, JDK 7, and JDK 8(Note: JDK7 and JDK8 are supported in 11.2.0.3 and 11.2.0.4 only);ojdbc5.jar with JDK 5