Location>code7788 >text

ORACLE PL/SQL Objects, Table Data Comparison Function Stored Procedure Simple Implementation

Popularity:797 ℃/2024-07-28 12:26:37

Recently, I helped to follow up an oracle11g upgrade to 19c project, due to the owner is not familiar with oracle upgrade related upgrade process, as well as upgrade the scope of the impact of matters related to the concern that the application downtime after the upgrade will lead to inconsistent business data saved in the database. 🙂‍↔️

Although we have always emphasized to the owner that upgrade upgrade will only upgrade oracle binary installation files and database system data dictionary related content for upgrade, will not modify the business data, and we are migrating + upgrading will not modify the content of the source library, there are fallback options. 😎

However, the owner indicated that he needed to provide a data validation program, a process that could not be missing, and had no choice but to provide a data comparison program:

  • Build 2 DG libraries from production backup sets + archives, library A for upgrading 19C and library B for retaining original data.
  • After the upgrade of library A is completed, build DBLINK and library B for data comparison, and query whether the data of library A and library B will be inconsistent after the upgrade.

The following two stored procedures are provided to determine whether there are differences in the data before and after the calibration upgrade:

  • diff_plobj_proc: This procedure compares all objects (PL/SQL objects, tables, views, sequences, etc.) of the business user at the source and target.
  • diff_row_proc: This procedure compares the data of all the table objects of the business users at the source and target ends to see whether they are consistent, and the difference set is inconsistent if it is not equal to 0.

 

diff_plobj_proc process code:

-------------------------------- Note: --------------------------------------------
The diff_plobj_proc procedure is recommended to be executed on the target instance (19C) by SYS, system, or a user with the DBA role.
Because the diff_plobj_proc procedure logic does not take into account the business user's access to the system table access privileges, the target (19C) creates access to the source (11G).
Therefore, the database user who creates the DBLINK on the target side (19C) to access the source side (11G) should have SYSTEM or SYS or DBA privileges to access the DBA_series system tables.
To access the DBA_series system attempts.
-------------------------------- Note: --------------------------------------------

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 object comparison table';
COMMENT ON COLUMN diff_plobj_table.source_schema_name       IS 'source_schema_name Source business schema name';
COMMENT ON COLUMN diff_plobj_table.source_obj_name          IS 'source_obj_type Source object name';
COMMENT ON COLUMN diff_plobj_table.source_obj_type          IS 'source_obj_type source_object_type';
COMMENT ON COLUMN diff_plobj_table.source_status            IS 'source_status Source object status';
COMMENT ON COLUMN diff_plobj_table.target_schema_name       IS 'target_schema_name Target business schema name';
COMMENT ON COLUMN diff_plobj_table.target_obj_name          IS 'target_obj_type Target object name';
COMMENT ON COLUMN diff_plobj_table.target_obj_type          IS 'target_obj_type target_object_type';
COMMENT ON COLUMN diff_plobj_table.target_status            IS 'target_status target_object_status';
COMMENT ON COLUMN diff_plobj_table.proc_exce_time            IS 'diff_plobj_proc procedure run time';




-- The diff_plobj_proc process compares the objects of business users in the source and target segments
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
    /* Uniformly convert incoming parameters to uppercase*/
    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);

    -- Define a ref cursor variable to receive the result set from v_sql.
    v_sql_ref_cursor SYS_REFCURSOR;

    -- Define a record type to hold the result set returned by 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';

    /* Determine whether the incoming v_source_db_link_name is valid, and report an error if it is not.*/
    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 is not working, please check if dblink is available!');
    END;

    /* Dynamic SQL Splicing*/
    v_sql := 'SELECT DISTINCT ' ||
            ' 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, ' ||
            ' AS source_status, ' ||
            ' 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, ' ||
            ' 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';
    

    -- Open cursor and execute dynamic SQL
    OPEN v_sql_ref_cursor FOR v_sql;

    -- Traversing a Cursor with a LOOP Loop
    LOOP
        FETCH v_sql_ref_cursor INTO v_sql_record_row;
        EXIT WHEN v_sql_ref_cursor%NOTFOUND;

        -- Insert into diff_plobj_table table
        INSERT INTO diff_plobj_table VALUES v_sql_record_row;
    END LOOP;
    CLOSE v_sql_ref_cursor;

    COMMIT;
END;
/



-- Execute the stored procedure , my environment Source and target business SCHEMA is not the same, the production environment is the same.
BEGIN
    diff_plobj_proc(
        p_source_db_link_name => 'SCOTT2_LINK',
        p_source_schema_name  => 'SCOTT2',
        p_target_schema_name  => 'SCOTT3');
END;
/


-- The diff_plobj_table lookup table gets a breakdown of the objects at the source and target.
SELECT * FROM diff_plobj_table;




-- Query source and target object data comparison
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 procedure code:

-------------------------------- Note: --------------------------------------------
The diff_row_proc procedure is recommended to be executed on the target instance (19C) by SYS, system, or a user with the DBA role.
Because the diff_row_proc procedure logic does not take into account the business user's access to the system table access rights.
Therefore, the database user who creates the DBLINK on the target (19C) to access the DBLINK on the source (11G) should have SYSTEM or SYS or DBA privileges to access the DBA_series system tables.
To access the DBA_series system attempts.
-------------------------------- Note: --------------------------------------------

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 'Comparison of source and target segment table row data discrepancy table';
COMMENT ON COLUMN diff_row_table.source_schema_name            IS 'source_schema_name Source business schema name';      
COMMENT ON COLUMN diff_row_table.source_table_name             IS 'source_table_name Source table object name';      
COMMENT ON COLUMN diff_row_table.source_table_row_total_cnt    IS 'source_table_row_total_cnt source_table_object_row_total_cnt';  
COMMENT ON COLUMN diff_row_table.target_schema_name             IS 'target_schema_name Target business schema name';      
COMMENT ON COLUMN diff_row_table.target_table_name             IS 'target_table_name Target table object name';      
COMMENT ON COLUMN diff_row_table.target_table_row_total_cnt    IS 'target_table_row_total_cnt target_table_row_total_cnt target_table_object_row_total_cnt';     
COMMENT ON COLUMN diff_row_table.source_target_diff_row        IS 'Difference in rows between source and target table comparison by primary key';     
COMMENT ON COLUMN diff_row_table.proc_exce_time                IS 'diff_row_table procedure run time';



-- Procedure 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);

    -- Array variable holding the table name of the source table
    v_source_table_name_arr DBMS_SQL.VARCHAR2_TABLE;

    -- Array variable holding the name of the table on the target
    v_target_table_name_arr DBMS_SQL.VARCHAR2_TABLE;

    -- Get all table name SQL variables of the business user at the source (dynamic SQL)
    v_source_table_sqlstr VARCHAR2(4000);

    -- Get all table name SQL variables for business users on the target side (dynamic SQL)
    v_target_table_sqlstr VARCHAR2(4000);

    -- Define a ref cursor variable to receive the result set returned by v_source_table_sqlstr.
    v_source_sql_ref_cursor SYS_REFCURSOR;

    -- Define a ref cursor variable to receive the result set returned by v_target_table_sqlstr.
    v_target_sql_ref_cursor SYS_REFCURSOR;

    -- Define variables to store the number of rows and the result count of the MINUS operation.
    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';

    /* Determine whether the incoming v_source_db_link_name is valid, and report an error if it is not.*/
    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 is not working, please check if dblink is available!');
    END;

    -- Get business table name from source SQL
    v_source_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' ||
                             v_source_db_link_name || ' a WHERE  = ''' ||
                             v_source_schema_name || ''' ORDER BY NUM_ROWS';

    DBMS_OUTPUT.PUT_LINE(v_source_table_sqlstr);
    -- Target get business table name SQL
    v_target_table_sqlstr := 'SELECT a.TABLE_NAME FROM DBA_TABLES' ||
                             ' a WHERE  = ''' ||
                             v_target_schema_name || ''' ORDER BY NUM_ROWS';

    -- Open cursor and execute dynamic 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 to batch insert the result set into an array
    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 cursor
    CLOSE v_source_sql_ref_cursor;
    CLOSE v_target_sql_ref_cursor;

    -- Compare table names and perform MINUS operations
    FOR i IN 1..v_source_table_name_arr.COUNT LOOP
            v_match_found := FALSE; -- Reset flag
            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;

                        -- Get source table rows
                        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;

                        -- Get the number of table rows on the target
                        v_sql := 'SELECT COUNT(*) FROM ' || v_target_schema_name || '.' || v_target_table_name_arr(j);
                        EXECUTE IMMEDIATE v_sql INTO v_target_row_count;

                        -- Perform MINUS operations and store the results
                        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;

                        -- Insert results into 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;

                        -- Jumping out of the inner circle
                        EXIT;
                    END IF;
                END LOOP;

            -- If a matching table name is not found, output a message and insert a record
            IF NOT v_match_found THEN
                DBMS_OUTPUT.PUT_LINE('Source table ' || v_source_table_name_arr(i) || ' has no matching target table');

                -- Insert records that do not match the 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, -- Number of source table rows
                             v_target_schema_name,
                             NULL, -- Target Table Name
                             NULL, -- Number of table rows on the target
                             NULL, -- Difference in number of rows between source and target table comparison
                             SYSDATE
                         );
                COMMIT;
            END IF;
        END LOOP;

END diff_row_proc;


-- Call diff_row_proc to compare the source and target, this procedure takes a while to run (possibly a long time)
BEGIN
    diff_row_proc( 
    p_source_db_link_name => 'SCOTT2_LINK', 
    p_source_schema_name =>  'SCOTT2', 
    p_target_schema_name => 'SCOTT3'
    );
END;
/


-- The diff_row_table table can be watched continuously during the
-- If SOURCE_TARGET_DIFF_ROW has a value of -999, it means that the source and target tables contain large fields, and you need to manually get the primary key or the unique column for the MINUS to perform a difference set comparison.
-- If SOURCE_TARGET_DIFF_ROW has a value of > 0, it indicates that the source and target table data do not match.
SELECT * FROM  diff_row_table WHERE SOURCE_TARGET_DIFF_ROW <> 0;

Recently, I have been writing c, pl/sql have not written for a long time, the above two processes took my brother more than 3 hours to write out 😅, really pick up a door to lose a door, drunk 😵‍💫.