【YashanDB知识库】对比Oracle和YashanDB对象不一致的方法

前言

通过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 sys.dba_objects; create table oracle.dba_tables sys.dba_tables; create table oracle.dba_indexes 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 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 sys.dba_sequences; create table oracle.dba_source 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 sys.dba_objects; create table yashan.dba_tables sys.dba_tables; create table yashan.dba_indexes 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 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 sys.dba_sequences; create table yashan.dba_source 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  o.owner = y.owner  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  o.owner = y.owner  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  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  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  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')  dc.OWNER = dcc.OWNER  dc.TABLE_NAME dcc.TABLE_NAME  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')  dc.OWNER = dcc.OWNER  dc.TABLE_NAME = dcc.TABLE_NAME  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  o.TABLE_NAME y.TABLE_NAME  o.CONSTRAINT_TYPE y.CONSTRAINT_TYPE  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') ;