【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