Anonymous Banner procedure to check XE surrogate ids against their sequence for issues.

XeSequenceCheck.sql

DECLARE

/*
    Banner XE Sequence Checker
    This procedure checks all tables in the given schema for the condition where
    the surrogate id sequence lags the maximum surrogate id.  This situation will
    eventually cause inserts into the table to fail with an unique constraint
    violation on the surrogate id.
    
    This condition comes about when data rows are copied from one region to another
    and the source surrogate id column is copied and passed along with the rest of
    the row.  Surrogate id's should not be copied.  It should be left up to the
    destination region to populate the surrogate id column (via the surrogate id
    trigger) from the sequence in the destination region.
    
    This procedure has one side effect.  Since it requires a select of nextval for
    all sequences it tests, all sequences queried will be incremented whenever it
    is run.
*/

schema_owner    VARCHAR(50);
tbl_name        VARCHAR(100);
seq_exists      int;
seq_num         int;
sid_max         int;
sid_qry         VARCHAR(200);
seq_name        VARCHAR(100);
seq_exist_qry   VARCHAR(200);
seq_num_qry     VARCHAR(200);
seq_chk         VARCHAR(200);

BEGIN
    -- prompt for schema
    schema_owner := '&schema_owner';

    -- query for tables in the schema
    -- modify this query to limit the output / number of tables checked
    FOR tbl_row IN (
        select *
        from all_tables
        where owner = schema_owner
        --and table_name like 'STV%'      -- limit tables to check here <<<
        order by table_name
        )
    LOOP

        -- Create table and sequence names
        tbl_name := tbl_row.table_name;
        seq_name := tbl_name || '_SURROGATE_ID_SEQUENCE' ;

        -- prepare queries
        seq_exist_qry := 'select 1 from all_sequences where sequence_name = ''' || seq_name || '''' || ' and sequence_owner = ''' || schema_owner || '''' ;
        sid_qry := 'select max(' || tbl_name || '_SURROGATE_ID) from ' || tbl_name ;
        seq_num_qry := 'select ' || schema_owner || '.' || seq_name || '.nextval from dual' ;
        
        --DBMS_OUTPUT.PUT_LINE(seq_exist_qry);  -- debug

        BEGIN
        -- check for existence of the sequence
        EXECUTE IMMEDIATE seq_exist_qry into seq_exists;
        
            -- get the sequence nextval
            --DBMS_OUTPUT.PUT_LINE(seq_num_qry);    -- debug
            EXECUTE IMMEDIATE seq_num_qry into seq_num;

                -- get the max surrogate id
                --DBMS_OUTPUT.PUT_LINE(sid_qry);    -- debug
                EXECUTE IMMEDIATE sid_qry INTO sid_max;
                
                -- the sequence nextval should be at least as great as the max surrogate id
                case
                when sid_max is null then
                    seq_chk := ' OK - no data in table.';
                when seq_num >= sid_max then
                    seq_chk := ' OK.';
                else
                    seq_chk := ' ' || tbl_name || ' has a problem! <<<<<<<<<<<<<<<<';
                end case;
            

            DBMS_OUTPUT.PUT_LINE(schema_owner || '.' || tbl_name || ' max sid = ' || sid_max ||
                ' sequence = ' || seq_num || seq_chk);

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE(schema_owner || '.' || tbl_name || ' No sequence found');
            WHEN OTHERS THEN
                raise_application_error(-20001, schema_owner || '.' || tbl_name||' encountered an error - '||SQLCODE||' -ERROR- '||SQLERRM);
        END;
    
    END LOOP;
    
END;