ORACLE PL/SQL 对象、表数据对比功能存储过程简单实现
最近帮忙跟进个oracle11g upgrade 升级到19c 的项目，由于业主方不太熟悉oracle upgrade相关升级流程，以及升级影响范围相关的事项，担心应用停机升级以后会导致数据库保存的业务数据不一致。🙂↔️
虽然我们一直跟业主方强调，upgrade 升级只会升级oracle 二进制安装文件和数据库系统数据字典相关的内容进行升级，不会修改业务数据，而且我们是迁移+升级不会修改源库内容，有回退方案。😎
但是业主表示需要提供数据验证的方案，这个流程不能缺，没办法只能提供数据对比方案：
- 通过生产的备份集+归档搭建2套DG库，A库用于升级19C，B库用于保留原始数据。
- A库升级完成以后搭建DBLINK和B库进行数据比对，查询A库升级后和B库数据是否会不一致。
以下提供两个存储过程来判断校验升级前后数据是否有差异：
- diff_plobj_proc：该存储过程对比源端、目标端业务用户所有的对象（PL/SQL对象，表，视图，序列等等）。
- diff_row_proc：该存储过程对比源端、目标端业务用户所有的表对象的数据是否一致，差集如果不等于0为数据不一致。
diff_plobj_proc 过程代码：
--------------------------------注意：-------------------------------------------- diff_plobj_proc 过程建议在目标端实例（19C）上SYS、system 或者具有DBA角色的用户执行。 因为 diff_plobj_proc 存储过程逻辑没有考虑到业务用户对于系统表访问权限这块的访问， 所以目标端（19C）创建访问源端（11G）的DBLINK使用的数据库用户最好是有SYSTEM 或者 SYS 或者 DBA的权限， 才能访问DBA_系列的系统试图。 --------------------------------注意：-------------------------------------------- drop TABLE diff_plobj_table; CREATE TABLE diff_plobj_table( source_schema_name VARCHAR2(200), source_obj_name VARCHAR2(200) , source_obj_type VARCHAR2(200) , source_status VARCHAR2(200) , target_schema_name VARCHAR2(200) , target_obj_name VARCHAR2(200) , target_obj_type VARCHAR2(200) , target_status VARCHAR2(200), proc_exce_time DATE ); COMMENT ON TABLE diff_plobj_table IS 'plsql对象比对表'; COMMENT ON COLUMN diff_plobj_table.source_schema_name IS 'source_schema_name 源端业务模式名'; COMMENT ON COLUMN diff_plobj_table.source_obj_name IS 'source_obj_type 源端对象名称'; COMMENT ON COLUMN diff_plobj_table.source_obj_type IS 'source_obj_type 源端对象类型'; COMMENT ON COLUMN diff_plobj_table.source_status IS 'source_status 源端对象状态'; COMMENT ON COLUMN diff_plobj_table.target_schema_name IS 'target_schema_name 目标端业务模式名'; COMMENT ON COLUMN diff_plobj_table.target_obj_name IS 'target_obj_type 目标端对象名称'; COMMENT ON COLUMN diff_plobj_table.target_obj_type IS 'target_obj_type 目标端对象类型'; COMMENT ON COLUMN diff_plobj_table.target_status IS 'target_status 目标端对象状态'; COMMENT ON COLUMN diff_plobj_table.proc_exce_time IS 'diff_plobj_proc 存储过程运行的时间'; -- diff_plobj_proc 过程对比源端、目标段的业务用户的对象 CREATE OR REPLACE PROCEDURE diff_plobj_proc( p_source_db_link_name IN VARCHAR2, p_source_schema_name IN VARCHAR2, p_target_schema_name IN VARCHAR2 ) IS /* 统一将传进来的参数转换成大写 */ v_source_db_link_name VARCHAR2(200) := '@' || UPPER(p_source_db_link_name); v_source_schema_name VARCHAR2(200) := UPPER(p_source_schema_name); v_target_schema_name VARCHAR2(200) := UPPER(p_target_schema_name); v_sql VARCHAR2(4000); v_link_name_flag VARCHAR2(10); -- 定义个 ref 游标变量用来接收 v_sql 返回的结果集 v_sql_ref_cursor SYS_REFCURSOR; -- 定义个 record 类型用来保存 v_sql 返回的结果集 TYPE v_sql_record_type IS RECORD ( source_schema_name VARCHAR2(500), source_obj_name VARCHAR2(500), source_obj_type VARCHAR2(500), source_status VARCHAR2(500), target_schema_name VARCHAR2(500), target_obj_name VARCHAR2(500), target_obj_type VARCHAR2(500), target_status VARCHAR2(500), proc_exce_time DATE ); v_sql_record_row v_sql_record_type; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE diff_plobj_table'; /* 判断传入的 v_source_db_link_name 是否有效，如果无效报错 */ BEGIN v_sql := 'SELECT 1 FROM DUAL' || v_source_db_link_name; EXECUTE IMMEDIATE v_sql INTO v_link_name_flag; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, v_source_db_link_name || '：dblink无效,请检查dblink是否可用!'); END; /* 动态 SQL 拼接 */ v_sql := 'SELECT DISTINCT ' || 'a.owner AS source_schema_name, ' || '(CASE ' || ' WHEN a.object_type = ''INDEX'' THEN ' || ' (SELECT LISTAGG(''IDX_'' || COLUMN_NAME, ''_'') WITHIN GROUP (ORDER BY COLUMN_POSITION) ' || ' FROM ALL_IND_COLUMNS aic ' || ' WHERE INDEX_NAME = a.object_name) ' || ' ELSE a.object_name ' || ' END) AS source_obj_name, ' || 'a.object_type AS source_obj_type, ' || 'a.status AS source_status, ' || 'b.owner AS target_schema_name, ' || '(CASE ' || ' WHEN b.object_type = ''INDEX'' THEN ' || ' (SELECT LISTAGG(''IDX_'' || COLUMN_NAME, ''_'') WITHIN GROUP (ORDER BY COLUMN_POSITION) ' || ' FROM ALL_IND_COLUMNS bic ' || ' WHERE INDEX_NAME = b.object_name) ' || ' ELSE b.object_name ' || ' END) AS target_obj_name, ' || 'b.object_type AS target_obj_type, ' || 'b.status AS target_status, ' || 'SYSDATE AS proc_exce_time ' || 'FROM (SELECT owner, object_name, object_type, status FROM DBA_OBJECTS' || v_source_db_link_name || ' WHERE owner = ''' || v_source_schema_name || ''') a ' || 'FULL JOIN (SELECT owner, object_name, object_type, status FROM DBA_OBJECTS WHERE OWNER = ''' || v_target_schema_name || ''') b ' || 'ON a.OBJECT_NAME = b.OBJECT_NAME AND a.OBJECT_TYPE = b.OBJECT_TYPE'; -- 打开游标并执行动态 SQL OPEN v_sql_ref_cursor FOR v_sql; -- 使用 LOOP 循环遍历游标 LOOP FETCH v_sql_ref_cursor INTO v_sql_record_row; EXIT WHEN v_sql_ref_cursor%NOTFOUND; -- 插入 diff_plobj_table 表 INSERT INTO diff_plobj_table VALUES v_sql_record_row; END LOOP; CLOSE v_sql_ref_cursor; COMMIT; END; / -- 执行存储过程 ，我的环境 源端和目标端的 业务SCHEMA 不一样，生产环境是一样的。 BEGIN diff_plobj_proc( p_source_db_link_name => 'SCOTT2_LINK', p_source_schema_name => 'SCOTT2', p_target_schema_name => 'SCOTT3'); END; / -- 查询表 diff_plobj_table 能获取源端和目标端的对象明细。 SELECT * FROM diff_plobj_table; -- 查询源端、目标端对象数据比对 SELECT * FROM (SELECT COUNT(1) source_cnt, source_obj_type FROM diff_plobj_table GROUP BY source_obj_type) so FULL JOIN (SELECT COUNT(1) target_cnt, TARGET_OBJ_TYPE FROM diff_plobj_table GROUP BY TARGET_OBJ_TYPE) tg ON so.SOURCE_OBJ_TYPE = tg.TARGET_OBJ_TYPE ORDER BY 1;
diff_row_proc 过程代码：
--------------------------------注意：-------------------------------------------- diff_row_proc 过程建议在目标端实例（19C）上SYS、system 或者具有DBA角色的用户执行。 因为 diff_row_proc 存储过程逻辑没有考虑到业务用户对于系统表访问权限这块的访问， 所以目标端（19C）创建访问源端（11G）的DBLINK使用的数据库用户最好是有SYSTEM 或者 SYS 或者 DBA的权限， 才能访问DBA_系列的系统试图。 --------------------------------注意：-------------------------------------------- drop TABLE diff_row_table; CREATE TABLE diff_row_table( source_schema_name VARCHAR2(200), source_table_name VARCHAR2(200) , source_table_row_total_cnt INT, target_schema_name VARCHAR2(200) , target_table_name VARCHAR2(200) , target_table_row_total_cnt INT , source_target_diff_row INT, proc_exce_time DATE ); COMMENT ON TABLE diff_row_table IS '对比源端、目标段表行数据差异表'; COMMENT ON COLUMN diff_row_table.source_schema_name IS 'source_schema_name 源端业务模式名'; COMMENT ON COLUMN diff_row_table.source_table_name IS 'source_table_name 源端表对象名称'; COMMENT ON COLUMN diff_row_table.source_table_row_total_cnt IS 'source_table_row_total_cnt 源端表对象行数统计'; COMMENT ON COLUMN diff_row_table.target_schema_name IS 'target_schema_name 目标端业务模式名'; COMMENT ON COLUMN diff_row_table.target_table_name IS 'target_table_name 目标端表对象名称'; COMMENT ON COLUMN diff_row_table.target_table_row_total_cnt IS 'target_table_row_total_cnt 目标端表对象行数统计'; COMMENT ON COLUMN diff_row_table.source_target_diff_row IS '源端和目标端表对比行数差异，通过主键对比'; COMMENT ON COLUMN diff_row_table.proc_exce_time IS 'diff_row_table 存储过程运行的时间'; -- 存储过程 diff_row_proc CREATE OR REPLACE PROCEDURE diff_row_proc ( p_source_db_link_name IN VARCHAR2, p_source_schema_name IN VARCHAR2, p_target_schema_name IN VARCHAR2 ) AS v_source_db_link_name VARCHAR2(200) := '@' || UPPER(p_source_db_link_name); v_source_schema_name VARCHAR2(200) := UPPER(p_source_schema_name); v_target_schema_name VARCHAR2(200) := UPPER(p_target_schema_name); v_link_name_flag CHAR; v_sql VARCHAR2(4000); -- 保存源端表表名的数组变量 v_source_table_name_arr DBMS_SQL.VARCHAR2_TABLE; -- 保存目标端表名的数组变量 v_target_table_name_arr DBMS_SQL.VARCHAR2_TABLE; -- 获取源端业务用户所有表名SQL变量（动态SQL） v_source_table_sqlstr VARCHAR2(4000); -- 获取目标端业务用户所有表名SQL变量（动态SQL） v_target_table_sqlstr VARCHAR2(4000); -- 定义 ref 游标变量用来接收 v_source_table_sqlstr 返回的结果集 v_source_sql_ref_cursor SYS_REFCURSOR; -- 定义 ref 游标变量用来接收 v_target_table_sqlstr 返回的结果集 v_target_sql_ref_cursor SYS_REFCURSOR; -- 定义变量存储行数和 MINUS 操作的结果计数 v_source_row_count PLS_INTEGER; v_target_row_count PLS_INTEGER; v_diff_count PLS_INTEGER; v_match_found BOOLEAN := FALSE; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE DIFF_ROW_TABLE'; /* 判断传入的 v_source_db_link_name 是否有效，如果无效报错 */ BEGIN v_sql := 'SELECT 1 FROM DUAL' || v_source_db_link_name; EXECUTE IMMEDIATE v_sql INTO v_link_name_flag; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, v_source_db_link_name || '：dblink无效,请检查dblink是否可用!'); END; -- 源端获取业务表名 SQL v_source_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' || v_source_db_link_name || ' a WHERE a.owner = ''' || v_source_schema_name || ''' ORDER BY NUM_ROWS'; DBMS_OUTPUT.PUT_LINE(v_source_table_sqlstr); -- 目标端获取业务表名 SQL v_target_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' || ' a WHERE a.owner = ''' || v_target_schema_name || ''' ORDER BY NUM_ROWS'; -- 打开游标并执行动态 SQL OPEN v_source_sql_ref_cursor FOR v_source_table_sqlstr; OPEN v_target_sql_ref_cursor FOR v_target_table_sqlstr; -- 使用 BULK COLLECT INTO 将结果集批量插入数组 FETCH v_source_sql_ref_cursor BULK COLLECT INTO v_source_table_name_arr; FETCH v_target_sql_ref_cursor BULK COLLECT INTO v_target_table_name_arr; -- 关闭游标 CLOSE v_source_sql_ref_cursor; CLOSE v_target_sql_ref_cursor; -- 比较表名并进行 MINUS 操作 FOR i IN 1..v_source_table_name_arr.COUNT LOOP v_match_found := FALSE; -- 重置标志 FOR j IN 1..v_target_table_name_arr.COUNT LOOP IF v_source_table_name_arr(i) = v_target_table_name_arr(j) THEN v_match_found := TRUE; -- 获取源端表行数 v_sql := 'SELECT COUNT(*) FROM ' || v_source_schema_name || '.' || v_source_table_name_arr(i) || v_source_db_link_name; EXECUTE IMMEDIATE v_sql INTO v_source_row_count; -- 获取目标端表行数 v_sql := 'SELECT COUNT(*) FROM ' || v_target_schema_name || '.' || v_target_table_name_arr(j); EXECUTE IMMEDIATE v_sql INTO v_target_row_count; -- 进行 MINUS 操作并存储结果 BEGIN BEGIN v_sql := 'SELECT /*+ PARALLEL(8) */COUNT(*) FROM (' || 'SELECT * FROM ' || v_source_schema_name || '.' || v_source_table_name_arr(i) || v_source_db_link_name || ' MINUS ' || 'SELECT * FROM ' || v_target_schema_name || '.' || v_target_table_name_arr(j) || ')'; EXECUTE IMMEDIATE v_sql INTO v_diff_count; EXCEPTION WHEN OTHERS THEN v_diff_count := -999; END; END; -- 将结果插入 diff_row_table INSERT INTO diff_row_table ( source_schema_name, source_table_name, source_table_row_total_cnt, target_schema_name, target_table_name, target_table_row_total_cnt, source_target_diff_row, proc_exce_time ) VALUES ( v_source_schema_name, v_source_table_name_arr(i), v_source_row_count, v_target_schema_name, v_target_table_name_arr(j), v_target_row_count, v_diff_count, SYSDATE ); COMMIT; -- 跳出内层循环 EXIT; END IF; END LOOP; -- 如果未找到匹配的表名，输出提示信息并插入记录 IF NOT v_match_found THEN DBMS_OUTPUT.PUT_LINE('Source table ' || v_source_table_name_arr(i) || ' has no matching target table'); -- 插入没有匹配表的记录 INSERT INTO diff_row_table ( source_schema_name, source_table_name, source_table_row_total_cnt, target_schema_name, target_table_name, target_table_row_total_cnt, source_target_diff_row, proc_exce_time ) VALUES ( v_source_schema_name, v_source_table_name_arr(i), NULL, -- 源端表行数 v_target_schema_name, NULL, -- 目标端表名 NULL, -- 目标端表行数 NULL, -- 源端和目标端表对比行数差异 SYSDATE ); COMMIT; END IF; END LOOP; END diff_row_proc; -- 调用 diff_row_proc 比对源端和目标端的数据 ，该存储过程需要跑一段时间（可能很久） BEGIN diff_row_proc( p_source_db_link_name => 'SCOTT2_LINK', p_source_schema_name => 'SCOTT2', p_target_schema_name => 'SCOTT3' ); END; / -- 期间可以持续观察 diff_row_table 表 -- 如果 SOURCE_TARGET_DIFF_ROW 有 -999 的值，则表示源端表和目标端表包含大字段，需要手工获取主键或者唯一列进行MINUS进行差集比对。 -- 如果 SOURCE_TARGET_DIFF_ROW 有 > 0 的值，则表示源端表和目标端表数据不一致。 SELECT * FROM diff_row_table WHERE SOURCE_TARGET_DIFF_ROW <> 0;
最近一直在写c，pl/sql 也好久没写了，上面俩过程花了哥3个多小时才写出来😅，真的是捡一门丢一门，醉了😵💫。
