Friday, July 24, 2009

Oracle: How to update all sequences

If you ever do data refreshes from production to development or test environments you may run into an issue where your sequences are not up to date. It seems that Oracle exports the sequences first, then the data. If your sequence numbers change during the entire export process you may get errors when using them in your refreshed schema.

To fix this problem you can try to find where your sequences are used and get the MAX(value) to find the next value. Alternatively you can just add a large random number, say 1,000,000, to all your sequences. For most users this will fix the problem and is very easy to do. Here's how:


-- Update all sequences
DECLARE
v_increase_by NUMBER;
v_bkp_increment_by NUMBER;
v_str VARCHAR2 (1000);
v_count NUMBER;
BEGIN
v_increase_by := 1000000;

FOR rec IN (SELECT *
FROM user_sequences) LOOP
-- Backup current incrementation number
v_bkp_increment_by := rec.increment_by;
-- Alter the sequence to increase by a defined amount
v_str := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_increase_by;

EXECUTE IMMEDIATE v_str;

-- Increase by that amount
v_str := 'select ' || rec.sequence_name || '.nextval from dual';

EXECUTE IMMEDIATE v_str
INTO v_count;

-- Reset the increment factor
v_str := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_bkp_increment_by;

EXECUTE IMMEDIATE v_str;
END LOOP;
END;
/

6 comments:

  1. I got this

    "Invalid start of code"

    what did I do wrong again =)

    ReplyDelete
    Replies
    1. You have to use it in a function or open it in a test window not sql window.

      Delete
  2. thanks very much, worked a treat and saved me a lot of bother

    ReplyDelete
  3. thanks, saved me a lot of trouble

    ReplyDelete
  4. A solution for the first option, updating based on the last primary key value:

    CREATE OR REPLACE
    PROCEDURE UPDATE_ALL_SEQUENCES AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    MAX_PK NUMBER;
    CURR_PK NUMBER;
    BEGIN

    FOR REC IN
    (
    SELECT
    TABS.TABLE_NAME,
    SEQS.SEQUENCE_NAME,
    COLS.COLUMN_NAME
    FROM
    USER_TABLES TABS,
    USER_TRIGGERS TRIGS,
    USER_DEPENDENCIES DEPS,
    USER_SEQUENCES SEQS,
    USER_CONSTRAINTS CONS,
    USER_CONS_COLUMNS COLS
    WHERE
    TRIGS.TABLE_NAME = TABS.TABLE_NAME AND
    DEPS.NAME = TRIGS.TRIGGER_NAME AND
    SEQS.SEQUENCE_NAME = DEPS.REFERENCED_NAME AND
    TABS.TABLE_NAME = COLS.TABLE_NAME AND
    COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME AND
    COLS.OWNER = CONS.OWNER AND
    CONS.CONSTRAINT_TYPE = 'P' AND
    COLS.COLUMN_NAME NOT LIKE '%_FK%'
    )
    LOOP

    EXECUTE IMMEDIATE 'SELECT MAX('|| REC.COLUMN_NAME ||') FROM ' || REC.TABLE_NAME || '' INTO MAX_PK;

    IF(MAX_PK IS NULL) THEN
    MAX_PK := 1;
    END IF;

    EXECUTE IMMEDIATE 'SELECT ' || REC.SEQUENCE_NAME ||'.NEXTVAL FROM DUAL' INTO CURR_PK;

    IF(MAX_PK > CURR_PK) THEN
    CURR_PK := MAX_PK - CURR_PK;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| REC.SEQUENCE_NAME ||' INCREMENT BY ' || CURR_PK;
    EXECUTE IMMEDIATE 'SELECT '|| REC.SEQUENCE_NAME ||'.NEXTVAL FROM DUAL' INTO MAX_PK;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| REC.SEQUENCE_NAME ||' INCREMENT BY 1';
    END IF;

    END LOOP;

    END UPDATE_ALL_SEQUENCES;

    ReplyDelete
  5. Hi..Mr.Martin

    Thanks a lot.. It works well.

    ReplyDelete