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

“本文内容来自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')

;
posted @   YashanDB  阅读(4)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· 腾讯ima接入deepseek-r1,借用别人脑子用用成真了~
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· DeepSeek崛起:程序员“饭碗”被抢,还是职业进化新起点?
· RFID实践——.NET IoT程序读取高频RFID卡/标签
点击右上角即可分享
微信分享提示