【YashanDB知识库】对比Oracle和YashanDB对象不一致的方法
1.【YashanDB知识库】yasql执行报错2.【YashanDB知识库】YCM Monit进程频繁误告警3.【YashanDB知识库】ycm托管主机报错libnsl.so.1 no such file or directory4.【YashanDB知识库】安装共享集群时报错:YAS-05721 invalid input parameter, reason: node name invalid5.【YashanDB知识库】调用外部UDF未能识别Java环境配置6.【YashanDB知识库】个别数据库用户无法登录数据库,报错 io fail:IO.EOF7.【YashanDB知识库】过期统计信息导致SQL执行计划变差8.【YashanDB知识库】隐藏参数怎么查看初始值9.【YashanDB知识库】原生mysql驱动配置连接崖山数据库10.【YashanDB知识库】yashandb升级后,yasboot restart出现版本回退、报错control file version incompatible11.【YashanDB知识库】解压安装包时报错"tar:Error is not recoverable"12.【YashanDB知识库】如何使用MySQL客户端链接YashanDB13.【YashanDB知识库】YashanDB 单机一主一备自动切换14.【YashanDB知识库】YashanDB到YashanDB手工元数据迁移15.【YashanDB知识库】YFS_修改AU_SIZE参数16.【YashanDB知识库】YashanDB备份恢复的两种渠道17.【YashanDB知识库】YashanDB获取统计信息18.【YashanDB知识库】关于表空间压缩19.【YashanDB知识库】如何更改自动统计信息收集任务20.【YashanDB知识库】如何利用数据库线程池稳定TP吞吐21.【YashanDB知识库】为什么YashanDB只有Geometry类型,没有Geogrephy类型22.【YashanDB知识库】用crontab实现YashanDB自动备份23.【YashanDB知识库】YashanDB与Oracle数据类型对齐24.【YashanDB知识库】多csv文件一键式导入yashandb25.【YashanDB知识库】手工迁移Doris数据到崖山分布式26.【YashanDB知识库】锁冲突检查27.【YashanDB知识库】重装新库及元数据和数据导出导入指导28.【YashanDB知识库】Mybatis-Plus适配崖山配置29.【YashanDB知识库】WAS配置YashanDB JDBC连接30.【YashanDB知识库】YashanDB安全设置示例31.【YashanDB知识库】YashanDB查询时间与实际时间默认少8小时32.【YashanDB知识库】Yasldr错误处理33.【YashanDB知识库】YCM和YMP密码重置34.【YashanDB知识库】YCM数据库托管遇到的问题35.【YashanDB知识库】备库扩缩容指导36.【YashanDB知识库】非YCM方式的自动备份方案示例37.【YashanDB知识库】归档日志清理38.【YashanDB知识库】私有maven使用崖山JDBC驱动39.【YashanDB知识库】通过导入导出修改用户名称40.【YashanDB知识库】字符集latin1的MySQL中文数据如何迁移到YashanDB41.【YashanDB知识库】透明故障转移配置指导42.【YashanDB知识库】MySQL的FIND_IN_SET如何在YashanDB改写
43.【YashanDB知识库】对比Oracle和YashanDB对象不一致的方法
44.【YashanDB知识库】MySQL的FROM_UNIXTIME和UNIX_TIMESTAMP如何在YashanDB改写45.【YashanDB知识库】YMP从oracle到yashan迁移评估报OCI相关错误46.【YashanDB 知识库】如何避免 yasdb 进程被 Linux OOM Killer 杀掉47.【YashanDB 知识库】DolphinScheduler 适配崖山 Python 驱动48.【YashanDB知识库】Flink CDC实时同步Oracle数据到崖山49.【YashanDB知识库】Kettle迁移MySQL到YashanDB50.【YashanDB知识库】Kettle迁移PostgreSQL到YashanDB51.【YashanDB知识库】MySQL返回结果集的存储过程的改写方法52.【YashanDB知识库】沙箱备库53.【YashanDB知识库】DataX迁移Hive到崖山分布式54.【YashanDB知识库】Springboot启动找不到崖山jdbc驱动的问题处理55.【YashanDB知识库】分布式LSC表修改字段56.【YashanDB知识库】扩展redo大小57.【YashanDB知识库】共享超过32000字节字符串插入CLOB类型方案58.【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法59.【YashanDB知识库】数据库升级后用yasboot在线扩充备节点出现报错60.【YashanDB知识库】共享从 MySQL异常处理CONTINUE HANDLER的改写方法61.【YashanDB知识库】服务器重启后启动yasom和yasagent进程时有告警62.【YashanDB知识库】MySQL field 函数的改写方法63.【YashanDB知识库】MySQL和YashanDB 隐式转换不一致引起的报错64.【YashanDB知识库】YashanDB 支持MySQL多表更新语句的解决方法65.【YashanDB知识库】从PostgreSQL迁移到YashanDB如何进行数据行数比对66.【YashanDB知识库】使用DataX工具迁移yashan数据到maxcompute67.【YashanDB知识库】如何从内存中获取SQL语句的执行计划68.两个案例带你看懂YashanDB执行计划69.YashanDB物理规格70.YashanDB逻辑规格71.YashanDB数据类型规格72.YashanDB与Oracle兼容性说明73.YashanDB个人版、标准版和企业版功能差异74.YashanDB体系架构75.YashanDB存储引擎76.YashanDB SQL引擎77.YashanDB PL引擎78.YashanDB主备高可用79.YashanDB共享集群80.YashanDB实例架构81.YashanDB数据库实例82.YashanDB内存体系83.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')
;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 Tracebit 用 C# 开发
· 腾讯ima接入deepseek-r1,借用别人脑子用用成真了~
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· DeepSeek崛起:程序员“饭碗”被抢,还是职业进化新起点?
· RFID实践——.NET IoT程序读取高频RFID卡/标签