“本文内容来自YashanDB官网，原文内容请见https://www.yashandb.com/newsinfo/7323370.html?templateId=1718516
前言
通过YMP迁移Oracle到YashanDB对象后，YMP已经提供了校验功能，推荐使用YMP的校验功能。如果需要更复杂的对比方法，可采用本文中的对比思路和方法。
思路
保存Oracle的DBA_*数据字典视图到某用户的表，然后通过YMP传到YashanDB，最后使用YashanDB的DBeaver图形界面通过SQL来查询不一致。
方法
1、在Oracle执行，保存Oracle的DBA_*数据字典视图到Oracle用户的表
create user oracle identified by welcome1;
grant dba, resource, connect to oracle;
create table oracle.dba_objects as select * from sys.dba_objects;
create table oracle.dba_tables as select * from sys.dba_tables;
create table oracle.dba_indexes as select * from sys.dba_indexes;
create table oracle.dba_ind_columns as SELECT INDEX_OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, COLUMN_LENGTH, DESCEND FROM SYS.DBA_IND_COLUMNS;
create table oracle.dba_constraints as SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME, INDEX_OWNER, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED FROM SYS.DBA_CONSTRAINTS;
create table oracle.dba_cons_columns as SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION FROM SYS.DBA_CONS_COLUMNS;
create table oracle.dba_types as select * from sys.dba_types;
create table oracle.dba_views as SELECT OWNER, VIEW_NAME, TEXT_LENGTH, TEXT_VC FROM SYS.DBA_VIEWS;
create table oracle.dba_sequences as select * from sys.dba_sequences;
create table oracle.dba_source as select * from sys.dba_source;
create table oracle.dba_triggers as SELECT OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, COLUMN_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, ACTION_TYPE FROM SYS.DBA_TRIGGERS;
2、通过YMP将Oracle用户下的第一步保存的数据字典信息表同步到YashanDB数据库的Oracle用户
3、在YashanDB执行，保存SYS的DBA_*数据字典视图到Yashan用户的表
create user yashan identified by welcome1;
grant dba to yashan;
create table yashan.dba_objects as select * from sys.dba_objects;
create table yashan.dba_tables as select * from sys.dba_tables;
create table yashan.dba_indexes as select * from sys.dba_indexes;
create table yashan.dba_ind_columns as SELECT INDEX_OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, COLUMN_LENGTH, DESCEND FROM SYS.DBA_IND_COLUMNS;
create table yashan.dba_constraints as SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME, INDEX_OWNER, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED FROM SYS.DBA_CONSTRAINTS;
create table yashan.dba_cons_columns as SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION FROM SYS.DBA_CONS_COLUMNS;
create table yashan.dba_types as select * from sys.dba_types;
create table yashan.dba_views as SELECT OWNER, VIEW_NAME, TEXT_LENGTH, TEXT FROM SYS.DBA_VIEWS;
create table yashan.dba_sequences as select * from sys.dba_sequences;
create table yashan.dba_source as select * from sys.dba_source;
create table yashan.dba_triggers as SELECT OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, COLUMN_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, ACTION_TYPE FROM SYS.DBA_TRIGGERS;
4、利用数据库能力，直接对比Oracle用户和YashanDB用户的差异。以下只是参考SQL，请根据需要更改以下SQL：
1）查询视图差异
SELECT
object_type,
owner,
object_name,
o_status,
y_status
FROM
(
SELECT
nvl(o.object_type, y.object_type) object_type,
nvl(o.owner, y.owner) owner,
nvl(o.object_name, y.object_name) object_name,
o.status o_status,
y.status y_status
FROM
(
SELECT
object_type,
owner,
object_name,
status
FROM
oracle.DBA_OBJECTS
WHERE
OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
AND OBJECT_TYPE = 'VIEW') o
FULL OUTER JOIN
(
SELECT
object_type,
owner,
object_name,
status
FROM
yashan.DBA_OBJECTS
WHERE
OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
AND OBJECT_TYPE = 'VIEW') y
ON
o.object_type = y.object_type
AND o.owner = y.owner
AND o.object_name = y.object_name
ORDER BY
o.object_type,
o.owner,
o.object_name
)
WHERE
nvl(o_status, 'NOT_EXISTS_IN_ORACLE') != nvl(y_status, 'NOT_EXISTS_IN_YASHAN')
;
2）查询PROCEDURE差异
SELECT
object_type,
owner,
object_name,
o_status,
y_status
FROM
(
SELECT
nvl(o.object_type, y.object_type) object_type,
nvl(o.owner, y.owner) owner,
nvl(o.object_name, y.object_name) object_name,
o.status o_status,
y.status y_status
FROM
(
SELECT
object_type,
owner,
object_name,
status
FROM
oracle.DBA_OBJECTS
WHERE
OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
AND OBJECT_TYPE = 'PROCEDURE') o
FULL OUTER JOIN
(
SELECT
object_type,
owner,
object_name,
status
FROM
yashan.DBA_OBJECTS
WHERE
OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
AND OBJECT_TYPE = 'PROCUDURE') y
ON
o.object_type = y.object_type
AND o.owner = y.owner
AND o.object_name = y.object_name
ORDER BY
o.object_type,
o.owner,
o.object_name
)
WHERE
nvl(o_status, 'NOT_EXISTS_IN_ORACLE') != nvl(y_status, 'NOT_EXISTS_IN_YASHAN')
3）查询INDEX差异
SELECT
TABLE_OWNER,
TABLE_NAME,
o_index_colum_list,
y_index_colum_list
FROM
(
SELECT
nvl(o.TABLE_OWNER, y.TABLE_OWNER) TABLE_OWNER,
nvl(o.TABLE_NAME, y.TABLE_NAME) TABLE_NAME,
o.index_colum_list o_index_colum_list,
y.index_colum_list y_index_colum_list
FROM
(
SELECT
TABLE_OWNER,
TABLE_NAME,
LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(
ORDER BY
COLUMN_POSITION) index_colum_list
FROM
oracle.DBA_IND_COLUMNS
WHERE
TABLE_OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
GROUP BY
TABLE_OWNER,
TABLE_NAME) o
FULL OUTER JOIN
(
SELECT
TABLE_OWNER,
TABLE_NAME,
LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(
ORDER BY
COLUMN_POSITION) index_colum_list
FROM
yashan.DBA_IND_COLUMNS
WHERE
TABLE_OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
GROUP BY
TABLE_OWNER,
TABLE_NAME) y
ON
o.TABLE_OWNER = y.TABLE_OWNER
AND o.TABLE_NAME = y.TABLE_NAME
ORDER BY
1,
2
)
WHERE
nvl(o_index_colum_list, 'NOT_EXISTS_IN_ORACLE') != nvl(y_index_colum_list, 'NOT_EXISTS_IN_YASHAN')
;
4）查询约束差异
SELECT
OWENR,
TABLE_NAME,
CONSTRAINT_TYPE,
o_cons_column_list,
y_cons_column_list
FROM
(
SELECT
NVL(o.OWNER, y.OWNER) OWENR,
NVL(o.TABLE_NAME, y.TABLE_NAME) TABLE_NAME,
NVL(o.CONSTRAINT_TYPE, y.CONSTRAINT_TYPE) CONSTRAINT_TYPE,
o_cons_column_list,
y_cons_column_list
FROM
(
SELECT
dc.OWNER,
dc.TABLE_NAME,
dc.CONSTRAINT_TYPE,
dc.CONSTRAINT_NAME,
listagg(dcc.COLUMN_NAME, ',') WITHIN GROUP(
ORDER BY
dcc.POSITION) o_cons_column_list
FROM
ORACLE.DBA_CONSTRAINTS dc,
oracle.DBA_CONS_COLUMNS dcc
WHERE
dc.OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
AND dc.OWNER = dcc.OWNER
AND dc.TABLE_NAME = dcc.TABLE_NAME
AND dc.CONSTRAINT_NAME = dcc.CONSTRAINT_NAME
GROUP BY
dc.OWNER,
dc.TABLE_NAME,
dc.CONSTRAINT_TYPE,
dc.CONSTRAINT_NAME) o
FULL OUTER JOIN
(
SELECT
dc.OWNER,
dc.TABLE_NAME,
dc.CONSTRAINT_TYPE,
dc.CONSTRAINT_NAME,
listagg(dcc.COLUMN_NAME, ',') WITHIN GROUP(
ORDER BY
dcc.POSITION) y_cons_column_list
FROM
ORACLE.DBA_CONSTRAINTS dc,
oracle.DBA_CONS_COLUMNS dcc
WHERE
dc.OWNER IN ('HR', 'OE', 'SH', 'BI', 'PM', 'IX')
AND dc.OWNER = dcc.OWNER
AND dc.TABLE_NAME = dcc.TABLE_NAME
AND dc.CONSTRAINT_NAME = dcc.CONSTRAINT_NAME
GROUP BY
dc.OWNER,
dc.TABLE_NAME,
dc.CONSTRAINT_TYPE,
dc.CONSTRAINT_NAME) y
ON
o.OWNER = y.OWNER
AND o.TABLE_NAME = y.TABLE_NAME
AND o.CONSTRAINT_TYPE = y.CONSTRAINT_TYPE
AND o.o_cons_column_list = y.y_cons_column_list
)
WHERE
nvl(o_cons_column_list, 'NOT_EXISTS_IN_ORACLE') != nvl(y_cons_column_list, 'NOT_EXISTS_IN_YASHAN')
;
