ORACLE PL/SQL 对象、表数据对比功能存储过程简单实现

--------------------------------注意：--------------------------------------------
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 过程建议在目标端实例（19C）上SYS、system 或者具有DBA角色的用户执行。因为 diff_row_proc 存储过程逻辑没有考虑到业务用户对于系统表访问权限这块的访问，所以目标端（19C）创建访问源端（11G）的DBLINK使用的数据库用户最好是有SYSTEM 或者 SYS 或者 DBA的权限，才能访问DBA_系列的系统试图。--------------------------------注意：--------------------------------------------dropTABLEdiff_row_table;CREATETABLE 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 ONTABLEdiff_row_tableIS'对比源端、目标段表行数据差异表';COMMENTONCOLUMNdiff_row_table.source_schema_nameIS'source_schema_name 源端业务模式名'; COMMENT ONCOLUMNdiff_row_table.source_table_nameIS'source_table_name 源端表对象名称'; COMMENT ONCOLUMNdiff_row_table.source_table_row_total_cntIS'source_table_row_total_cnt 源端表对象行数统计'; COMMENT ONCOLUMNdiff_row_table.target_schema_nameIS'target_schema_name 目标端业务模式名'; COMMENT ONCOLUMNdiff_row_table.target_table_nameIS'target_table_name 目标端表对象名称'; COMMENT ONCOLUMNdiff_row_table.target_table_row_total_cntIS'target_table_row_total_cnt 目标端表对象行数统计'; COMMENT ONCOLUMNdiff_row_table.source_target_diff_rowIS'源端和目标端表对比行数差异，通过主键对比'; COMMENT ONCOLUMNdiff_row_table.proc_exce_timeIS'diff_row_table 存储过程运行的时间';-- 存储过程 diff_row_procCREATEORREPLACEPROCEDURE diff_row_proc ( p_source_db_link_name INVARCHAR2, p_source_schema_name INVARCHAR2, p_target_schema_name INVARCHAR2)ASv_source_db_link_nameVARCHAR2(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_sqlstrVARCHAR2(4000);--获取目标端业务用户所有表名SQL变量（动态SQL）v_target_table_sqlstrVARCHAR2(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;BEGINEXECUTEIMMEDIATE'TRUNCATE TABLE DIFF_ROW_TABLE';BEGIN v_sql :='SELECT 1 FROM DUAL'||v_source_db_link_name;EXECUTE IMMEDIATE v_sql INTO v_link_name_flag; EXCEPTION WHENOTHERSTHENraise_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';-- 打开游标并执行动态 SQLOPENv_source_sql_ref_cursorFORv_source_table_sqlstr;OPENv_target_sql_ref_cursorFORv_target_table_sqlstr;-- 使用 BULK COLLECT INTO 将结果集批量插入数组FETCHv_source_sql_ref_cursorBULKCOLLECTINTOv_source_table_name_arr;FETCHv_target_sql_ref_cursorBULKCOLLECTINTOv_target_table_name_arr;--关闭游标CLOSEv_source_sql_ref_cursor;CLOSEv_target_sql_ref_cursor;-- 比较表名并进行 MINUS 操作FORiIN1..v_source_table_name_arr.COUNT LOOP v_match_found :=FALSE;--重置标志FORjIN1..v_target_table_name_arr.COUNTLOOPIFv_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 INTOv_source_row_count;--获取目标端表行数 v_sql :='SELECT COUNT(*) FROM '||v_target_schema_name||'.'||v_target_table_name_arr(j);EXECUTE IMMEDIATE v_sql INTOv_target_row_count;-- 进行 MINUS 操作并存储结果BEGINBEGIN v_sql :='SELECT 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 WHENOTHERSTHEN v_diff_count :=-999;END;END;-- 将结果插入 diff_row_tableINSERTINTO 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;ENDIF;ENDLOOP;--如果未找到匹配的表名，输出提示信息并插入记录IFNOTv_match_foundTHENDBMS_OUTPUT.PUT_LINE('Source table '||v_source_table_name_arr(i)||' has no matching target table');--插入没有匹配表的记录INSERTINTO 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;ENDIF;ENDLOOP;ENDdiff_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*FROMdiff_row_tableWHERESOURCE_TARGET_DIFF_ROW<>0;